Search code examples
sqlcasesqldatetime

SQL query for CASE WHEN 2 dates match

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?

Solution

  • 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;

    enter image description here

    Demo

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