CODEWITHSUNDEEP
sql-serversql-server-2008t-sql
jankenshin2004
jankenshin2004

Reputation: 97

Select records based on 2 date columns

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

Upvotes: 0

Views: 112

Answers (4)

Christian Delos Reyes
Christian Delos Reyes

Reputation: 28

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

Upvotes: 0

Christian Delos Reyes
Christian Delos Reyes

Reputation: 28

You may use ISNULL() function in TSQL

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

Upvotes: 0

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67311

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!

Upvotes: 1

dhaninugraha
dhaninugraha

Reputation: 9

Is this what you are looking for?

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

Upvotes: 0

Related Questions