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?

give some examples of input and output

drum
– drum

2021-07-13 04:04:52 +00:00
Commented Jul 13, 2021 at 4:04 — drum
– drum, Commented Jul 13, 2021 at 4:04
Why should I tag my DBMS

user330315
– user330315

2021-07-13 06:57:00 +00:00
Commented Jul 13, 2021 at 6:57 — user330315
– user330315, Commented Jul 13, 2021 at 6:57

Tim Biegeleisen · Accepted Answer · 2021-07-13 04:15:00Z

2

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.

answered Jul 13, 2021 at 4:15

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

tushar · Accepted Answer · 2021-07-13 04:11:44Z

1

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

answered Jul 13, 2021 at 4:11

tushar

112 bronze badges

1 Comment

endo64 Over a year ago

That is correct, it should be CASE WHEN not CASE (WHEN.

David · Accepted Answer · 2021-07-13 04:13:29Z

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, 2021 at 4:13

David

4864 silver badges9 bronze badges

Collectives™ on Stack Overflow

SQL query for CASE WHEN 2 dates match

3 Answers 3

Demo

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related