SQL query for CASE WHEN 2 dates match

Question

New to SQL so sorry if this is a basic question. Looking to create a column that indicates a match in two dates (i.e. the date the record is created is identical to the date the record is expired).

My two date variables are timestamps in the format YYYY-MM-DD HH:MM:SS.SSS

Input examples:

|Created                 | Expiry                  |
|------------------------|-------------------------|
|2021-01-23 12:34:43.123 | 2021-04-18 15:24:17.432 |
|2021-02-19 10:01:01.231 | 2021-02-19 00:00:00.000 |

My query is looking to find the number of days between the two dates and if that equals 0 (i.e. same date in both), then code the Same_Day column as 1 otherwise 0.

SELECT
CASE (WHEN DATEDIFF(dd, CONVERT(VARCHAR(10), Expiry, 102), CONVERT(VARCHAR(10), Created, 102)) = 0 THEN 1 ELSE 0 END) AS Same_Day
FROM database

I'm trying to remove the time and keep the date info before finding the number of days between the dates.

Desired Output

|Created                 | Expiry                  |Same_Day |
|------------------------|-------------------------|---------|
|2021-01-23 12:34:43.123 | 2021-04-18 15:24:17.432 |0        |
|2021-02-19 10:01:01.231 | 2021-02-19 00:00:00.000 |1        |

However, I'm getting a warning message: Incorrect syntax near the keyword WHEN. Any leads, please?

[Why should I tag my DBMS](https://meta.stackoverflow.com/questions/388759/) — , Jul 13 '21 at 06:57

score 2 · Accepted Answer · answered Jul 13 '21 at 04:15

Since you want to compare the date components of the created and expiry timestamps, you should do so directly, without the use of DATEDIFF:

SELECT
    Created,
    Expiry,
    CASE WHEN CONVERT(VARCHAR(10), Expiry, 102) = CONVERT(VARCHAR(10), Created, 102)
         THEN 1 ELSE 0 END AS Same_Day
FROM database;

Demo

The DATEDIFF function operates on two date inputs, not two string dates.

score 1 · Answer 2 · answered Jul 13 '21 at 04:11

1

Remove opening bracket "(" after case and also remove closing bracket")" after end . May be it will solve error

answered Jul 13 '21 at 04:11

tushar

11
2

That is correct, it should be `CASE WHEN` not `CASE (WHEN`. – endo64 Jul 13 '21 at 12:06

score 0 · Answer 3 · answered Jul 13 '21 at 04:13

0

Just improve the formatting, and remove some bracket. Try this:

SELECT
    CASE WHEN DATEDIFF(dd, CONVERT(VARCHAR(10), Expiry, 102), CONVERT(VARCHAR(10), Created, 102)) = 0 
         THEN 1 
         ELSE 0 
    END AS Same_Day
FROM database

answered Jul 13 '21 at 04:13

GGG

486
4
9

SQL query for CASE WHEN 2 dates match

3 Answers3

Demo