Select records based on 2 date columns

Question

What will be the best script to get the records from a certain table with column createdDate and modifiedDate where modifiedDate is the only nullable column and is equal to date now/current date?

In case that there will be null values from modified date column, the script will get the created date.

The output should get all records with latest created or modified data. I tried these script below:

SELECT *
FROM table
WHERE ISNULL(CONVERT(date, ModifiedDate),
             CONVERT(date,CreatedDate)) = CONVERT(date, getdate())

or

SELECT *
FROM table
WHERE CASE WHEN ModifiedDate IS NULL
           THEN CONVERT(date, CreatedDate)
           ELSE CONVERT(date,ModifiedDate) END = CONVERT(date, getdate())

`convert(date,ModifiedDate)` ... I don't like this, because it implies that you are storing your date information in a non-date type. — Tim Biegeleisen, Nov 11 '16 at 07:36
I have to get the records with same date, I used Convert(date, datetime) to remove the Time, I tried also cast(ModifiedDate as date), I need to get records in the most fastest way.. — jankenshin2004, Nov 11 '16 at 07:37
May be you need to look into this post - http://stackoverflow.com/questions/707335/t-sql-cast-versus-convert — PowerStar, Nov 11 '16 at 07:48
Hi @RafalZiolkowski, i already tried the above scripts, I just need what would be the best script among the two or is there any option script faster than those above — jankenshin2004, Nov 11 '16 at 07:49
Based on the the post i provided earlier, you can choose based on your requirement. — PowerStar, Nov 11 '16 at 07:51
Than I think your version 1 is ok, or go with @Shnugo version. Version 2 is not the best approach as you rather want to avoid complex conditional logic in WHERE clause. It complicates query and usually you can achieve the same with less typing. Also potentially it might confuse SQL Query Analyzer. — , Nov 11 '16 at 08:19
@RafalZiolkowski The *version 1* uses `ISNULL()`. This will break the usage of indexes. With *millions of records* (see comment below answer *dhaninugraha*) any query without index will be slow... Read about *sargable*... — Shnugo, Nov 11 '16 at 08:23

Shnugo · Accepted Answer · 2016-11-11T07:59:57.540

1

If you just need this against today (= GETDATE()) this should be simple as this:

SELECT *
FROM tbl
WHERE  CAST(CreatedDate AS DATE) = CAST(GETDATE() AS DATE)
    OR (ModifiedDate IS NOT NULL AND CAST(ModifiedDate AS DATE)= CAST(GETDATE() AS DATE));

Some thoughts: It is very bad to use functions (here: ISNULL()) in predicats. The engine will not be able to apply existing indexes. Read about sargable. One exception is CAST(SomeDateTime AS DATE) which is sargable.

If you do not need this for any date (just for today), it should be enough to say: This row was created today or it was modified today.

Important Be sure that there are indexes for ModifiedDate and CreatedDate!

edited Nov 11 '16 at 07:59

answered Nov 11 '16 at 07:44

Shnugo

66,100
9
53
114

Hi @Shnugo, OR will affect the performance I think and yes there are indexes for those 2 columns – jankenshin2004 Nov 11 '16 at 07:52
@jankenshin2004 Why do you think so? Simple boolean logic (AND / OR ...) is the fastest possible. If there is an index on both columns a check on equality should be lightning fast, even with millions of rows... – Shnugo Nov 11 '16 at 07:53
Based on the data I experience right now, when I use OR versus the script on the above I posted, the execution time is too far and different. – jankenshin2004 Nov 11 '16 at 07:55
@jankenshin2004, please check, whether there are indexes on both columns or not and check their fragmentation. Your example with `ISNULL` cannot be fast... And the second example will be translated to something similar as mine. Check the execution plans. – Shnugo Nov 11 '16 at 07:59
@jankenshin2004 If there are very many `ModifiedDate`s with `NULL` it might help to exclude them. See my edit (last line with `OR`) – Shnugo Nov 11 '16 at 08:00
yes I also tried this and will test again to verify if this will be much faster. – jankenshin2004 Nov 11 '16 at 08:03
@jankenshin2004 Is this question solved? Do you need further help? Please allow me one hint: If this question is solved, it would be very kind of you, to tick the acceptance check below the (best) answer's vote counter. This will 1) mark this issue as solved 2) make it easier for followers to find the best solution 3) pay points to the answerer and 4) pay points to you. Once you've crossed the 15 points border yourself, you are - additionally - asked to vote on contributions. This is the SO-way to say thank you. Happy Coding! – Shnugo Nov 17 '16 at 07:50
Thanks @shnugo, I already use this code in my project and got the performance I wanted. – jankenshin2004 Nov 18 '16 at 03:19

score 0 · Answer 2 · answered Nov 11 '16 at 07:44

0

Is this what you are looking for?

SELECT
   someColumn1,
   someColumn2,
   CASE
       WHEN modifiedDate IS NULL THEN createdDate
       ELSE modifiedDate
   END AS someDate
FROM someTable

answered Nov 11 '16 at 07:44

dhaninugraha

9
1

i think no, because it will get all records. The script above has no filter.. if I use this, it will return millions of records. I just need the records for today – jankenshin2004 Nov 11 '16 at 07:46
Could you provide us with some sample data? – dhaninugraha Nov 11 '16 at 07:48

score 0 · Answer 3 · answered Nov 11 '16 at 08:59

0

You may use ISNULL() function in TSQL

SELECT * FROM table WHERE ISNULL(CONVERT(date,ModifiedDate), CONVERT(date, CreatedDate)) = CONVERT(date, getdate())

answered Nov 11 '16 at 08:59

Christian Delos Reyes

28
3

Please read the existing answers... The usage of `ISNULL()` in a `WHERE` clause is not *sargable*. The engine will not be able to apply indexes. This is something one should avoid... – Shnugo Nov 11 '16 at 09:02
Thanks for the info. How bout COALESCE() function? will it also break the usage of indexes? – Christian Delos Reyes Nov 11 '16 at 09:07
Yes, Almost any function will blind the optimizer. There are few exceptions like `CAST(SomeDateTime AS DATE)` but in most cases the function will lead to a *row-by-row* execution... – Shnugo Nov 11 '16 at 09:20
SELECT * FROM table WHERE ModifiedDate IS NOT NULL AND CONVERT(date,ModifiedDate) = CONVERT(date, getdate() UNION SELECT * FROM table WHERE ModifiedDate IS NULL AND CONVERT(date,CreatedDate) = CONVERT(date, getdate() – Christian Delos Reyes Nov 11 '16 at 09:21
There is absolutely no reason for your `UNION`... You will double the work for nothing... – Shnugo Nov 11 '16 at 09:58

Christian Delos Reyes · Answer 4 · 2016-11-11T09:25:38.380

0

If using function will affect the indexes, kindly try this one if this is efficient.

SELECT *
FROM table
WHERE ModifiedDate IS NOT NULL
AND CONVERT(date,ModifiedDate) = CONVERT(date, getdate()
UNION 
SELECT *
FROM table
WHERE ModifiedDate IS NULL
AND CONVERT(date,CreatedDate) = CONVERT(date, getdate()

edited Nov 11 '16 at 09:25

answered Nov 11 '16 at 09:19

Christian Delos Reyes

28
3

Select records based on 2 date columns

4 Answers4