DateDiff in SQL Server asking for help

Question

Hello everyone,

I am using SQL Server 2008. I have a table which has a datetime type column called CreateTime. I need to select all the records from this table whose CreateTime is more than 3 days and half an hour from current time (UTC time). I am using T-SQL store procedure.

BTW: The CreateTime column is some time in the past time.

I have taken quite some time to learn and search for help from MSDN for DateDiff, but cannot figure out. Could anyone show me a sample please?

thanks in advance, George

Answer 1

You need DATEADD:

WHERE DATEADD(minute, 4350, CreateTime) <= getutcdate()

Or, as you mentioned, you can use DATEDIFF:

WHERE DATEDIFF(minute, CreateTime, getutcdate()) <= 4350

(4350 is '3 days and 30 minutes' in minutes)

Answer 2

You can select and add a WHERE clause with a DATEDIFF using minutes:

SELECT (fields)
FROM (table)
WHERE 
  DATEDIFF(MINUTE, CREATETIME, getutcdate()) <= (3*24*60 + 30)

And of course, if you only wants those rows which are MORE than 3 days and 30 minutes away, just use the opposite:

WHERE 
  DATEDIFF(MINUTE, CREATETIME, getutcdate()) > (3*24*60 + 30)

A sample:

SELECT      
  DATEDIFF(MINUTE, '2009-08-01 08:00:00', getutcdate()),
  DATEDIFF(MINUTE, '2009-07-31 20:00:00', getutcdate()),
  DATEDIFF(MINUTE, '2009-07-23 20:00:00', getutcdate())

gives as result:

96    816    12337

So the first two dates are still within your 4350 minute bracket (less than 3 days and 30 minutes ago), while the third date is further away.

Marc

Answer 3

One minor quibble with the given answers, though they are correct. Don't apply the function to the column: apply the function to the comparison value.

If CREATETIME is indexed then it's a scan rather than seek with the function on the column. You don't need millions of rows for this to be a problem.

Adapting the answer of marc_s:

SELECT (fields)
FROM (table)
WHERE 
  CREATETIME <= DATEADD(MINUTE, - (3*24*60 + 30), getutcdate())