Date compare between two datetime columns and a constant-CodePudding

Lets say I have this following table:

 ------ ------------------------- ------------------------- 
| ID   | CreatedDate             | LastChangedDate         |
 ------ ------------------------- ------------------------- 
| 3965 | 2019-01-23 03:54:44.903 | 2021-03-12 06:24:45.390 |
 ------ ------------------------- ------------------------- 
| 3966 | 2019-01-23 03:55:37.160 | 2021-01-09 04:50:20.697 |
 ------ ------------------------- ------------------------- 
| 3967 | 2019-01-23 03:56:21.197 | 2020-05-11 06:10:14.203 |
 ------ ------------------------- ------------------------- 
| 3968 | 2019-01-23 03:57:07.943 | 2020-05-11 11:28:26.580 |
 ------ ------------------------- ------------------------- 
| 3969 | 2019-01-23 03:58:01.020 | NULL                    |
 ------ ------------------------- ------------------------- 
| 3970 | 2019-01-23 03:58:42.293 | 2021-05-11 09:57:54.553 |
 ------ ------------------------- ------------------------- 
| 4143 | 2019-03-19 04:23:08.003 | 2020-12-14 10:08:38.303 |
 ------ ------------------------- ------------------------- 
| 4144 | 2019-03-19 04:51:14.533 | 2020-12-14 10:05:11.867 |
 ------ ------------------------- ------------------------- 
| 4145 | 2019-03-19 05:16:28.980 | 2019-07-11 07:23:15.803 |
 ------ ------------------------- ------------------------- 
| 4146 | 2019-03-19 05:18:49.550 | 2020-01-02 09:12:13.597 |
 ------ ------------------------- ------------------------- 
| 4808 | 2019-09-17 05:44:54.587 | 2021-01-09 10:35:20.860 |
 ------ ------------------------- ------------------------- 
| 5243 | 2020-01-02 09:07:10.573 | 2021-02-01 16:06:51.770 |
 ------ ------------------------- ------------------------- 
| 5666 | 2020-08-12 07:16:20.617 | 2021-01-09 04:52:25.427 |
 ------ ------------------------- ------------------------- 
| 5877 | 2020-09-05 05:35:56.160 | 2021-01-09 04:51:43.707 |
 ------ ------------------------- -------------------------

Now lets say I want to see whether CreatedDate or LastChangedDate column is greater than '2021-01-09'. To do the comparison, I need to check if LastChangedDate is NULL then compare the given date with CreatedDate field, else compare with LastChangedDate field.

What I have tried so far:

DECLARE @test_date as varchar(20) = '2021-01-09'

SELECT ID, CreatedDate, LastChangedDate
FROM #temptable
WHERE CAST(ISNULL(LastChangedDate,CreatedDate) as date) > CAST(@test_date as date)

But it is not giving proper output.

What I want is:

 ------ ------------------------- ------------------------- 
| ID   | CreatedDate             | LastChangedDate         |
 ------ ------------------------- ------------------------- 
| 3967 | 2019-01-23 03:56:21.197 | 2020-05-11 06:10:14.203 |
 ------ ------------------------- ------------------------- 
| 3968 | 2019-01-23 03:57:07.943 | 2020-05-11 11:28:26.580 |
 ------ ------------------------- ------------------------- 
| 3969 | 2019-01-23 03:58:01.020 | NULL                    |
 ------ ------------------------- ------------------------- 
| 4143 | 2019-03-19 04:23:08.003 | 2020-12-14 10:08:38.303 |
 ------ ------------------------- ------------------------- 
| 4144 | 2019-03-19 04:51:14.533 | 2020-12-14 10:05:11.867 |
 ------ ------------------------- ------------------------- 
| 4145 | 2019-03-19 05:16:28.980 | 2019-07-11 07:23:15.803 |
 ------ ------------------------- ------------------------- 
| 4146 | 2019-03-19 05:18:49.550 | 2020-01-02 09:12:13.597 |
 ------ ------------------------- -------------------------

Also here is a sqlplayground with sample data

CodePudding user response：

In fact you shouldn't need any CASTs:

first define your variable as a date instead of a string - always use the correct datatype for the data you are storing.
second use logical operations (AND/OR) instead of COALESCE to make your query sargable i.e. able to use indexes. Anytime you use a function on a column in your WHERE clause you run the risk of preventing the use of indexes and slowing your query down.

DECLARE @test_date as date = '2021-01-09';

SELECT ID, CreatedDate, LastChangedDate
FROM #temptable
WHERE (LastChangedDate IS NOT NULL AND LastChangedDate > @test_date)
OR (LastChangedDate IS NULL AND CreatedDate > @test_date);

CodePudding user response：

I think you are comparing only date. So try with compare date and time both.

SELECT ID,CreatedDate, LastChangedDate
FROM #temptable WHERE 
 CAST(ISNULL(LastChangedDate,CreatedDate) as datetime) > CAST(@test_date as datetime)