MSSQL Server SQL Query With Date Subtraction Filter Clause
- Dominant language
- Rust
- Stars
- 20.8k
- Forks
- 2.1k
- Avg merge
- 1d 2h
- Merged PRs (30d)
- 181
Description
**Failed SQL**
Search for `Failed SQL` log message.
```
2023-09-12 08:48:50 Cube SQL Error: undefined
2023-09-12 08:48:50 --
2023-09-12 08:48:50 "SELECT first_name, last_name, leave_start_date, leave_end_date FROM employee_leaves WHERE leave_end_date - leave_start_date > 20"
2023-09-12 08:48:50 --
2023-09-12 08:48:50 {
2023-09-12 08:48:50 "appName": "DataGrip 2022.3.3",
2023-09-12 08:48:50 "protocol": "postgres",
2023-09-12 08:48:50 "apiType": "sql",
2023-09-12 08:48:50 "sanitizedQuery": "SELECT first_name, last_name, leave_start_date, leave_end_date FROM employee_leaves WHERE leave_end_date - leave_start_date > 20"
2023-09-12 08:48:50 }
```
**Logical Plan**
Search for `Can't rewrite plan` log message.
```
2023-09-12 08:48:50 Error during rewrite: Can't detect Cube query and it may be not supported yet. Please check logs for additional information.
```
**Version:**
0.33.53
**Additional context**
I'm using MS SQL Server as the data source. When I run the following query via the SQL API:
```sql
SELECT first_name, last_name, leave_start_date, leave_end_date
FROM employee_leaves
WHERE leave_end_date - leave_start_date > 20;
```
I get the described error. The error seems to be with the date filter condition. Removing the condition it works:
```sql
SELECT first_name, last_name, leave_start_date, leave_end_date
FROM employee_leaves
```
and results in the following data:
|first_name|last_name|leave_start_date |leave_end_date |
|----------|---------|--------------------------|--------------------------|
|XXX |YYYY |2017-07-02 00:00:00.000000|2017-07-09 00:00:00.000000|
|ZZZ |WWW |2017-07-16 00:00:00.000000|2017-07-17 00:00:00.000000|
I thought maybe the date subtraction is not supported in the filter clause but the following sample works:
```sql
WITH temp_table AS (SELECT 1)
SELECT 1
FROM temp_table
WHERE ('2022-01-22'::DATE - '2022-01-01'::DATE) > 20;
```
So not sure what is causing the issue.
Contributor guide
Assessment
This issue has not been assessed yet.