Date compare between two datetime columns and a constant

Question

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 JavaScript questions and answers, JavaScript questions pdf, JavaScript question bank, JavaScript questions and answers pdf, mcq on JavaScript pdf, JavaScript questions and solutions, JavaScript mcq Test , Interview JavaScript questions, JavaScript Questions for Interview, JavaScript MCQ (Multiple Choice Questions)

Answer 1

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