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:
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)
CodePudding user response:
In fact you shouldn't need any CAST
s:
- 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 ofCOALESCE
to make your query sargable i.e. able to use indexes. Anytime you use a function on a column in yourWHERE
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);