DateDiff in SQL Server

I didn't realize how difficult it can be to query on dates.  Because a date consists of a date and a time, when you want to see if a date is the current date, using the getdate() mechanism, it won't match because the time isn't exact.  How can you correct this?  Using the datediff method in SQL Server.  DateDiff takes two dates, and verifies certain parameters (the list is in the MSDN documentation link a few words over).  I've used it more in this context though:

select * from MyTable where datediff(dayofyear, RequestDate, getdate()) = 0

DateDiff returns an integer value if the dates match, which would equal zero, or if they don't, which will be a positive or negative number based on which date is greater.  This way, you can search based only on the date.  You can also use the day parameter to determine if it is the same day, and a variety of other terms defined in the list.  You need to use a mechanism like this, or one of the other date methods, to make date determinations.

Published Thursday, November 30, 2006 10:00 AM by bmains
Filed under:

Comments

# re: DateDiff in SQL Server

Thursday, November 30, 2006 11:10 PM by IDisposable

This is very bad advice, sorry to say! Firstly, your query will match the same day-of-year ACROSS years (i.e. 2/1/2006 matches 2/1/2007), which I doubt is what you intend...

You should NEVER, EVER do math against a column (and that means you, Mr. DateDiff).  This prevents the use of indexes.  Rather, you want to compare the datetime column (in whatever its actual form, with time included) against a range to insure that it's just an index seek, if possible.

Typically this can be done with a BETWEEN clause and using the "identity math" trick to drop the time from the GetDate() function.  

To get midnight of today (e.g. the beginning of today) you do:

SELECT DATEADD(dd, DATEDIFF(dd, 0, GetDate()), 0)

To get midnight tomorrow (e.g. the end of today+):

SELECT DATEADD(dd, DATEDIFF(dd, 0, GetDate()) + 1, 0)

So your select would be:

SELECT *

FROM MyTable

WHERE RequestDate >=  DATEADD(dd, DATEDIFF(dd, 0, GetDate()), 0)

AND RequestDate < DATEADD(dd, DATEDIFF(dd, 0, GetDate()) + 1, 0)

Since I like BETWEEN clauses, and they are inclusive, you really want the END of today. To get the end of today (e.g. tiniest bit before tomorrow, SQL resolution is 3milliseconds):

SELECT DATEADD(ms, -3, DATEADD(dd, DATEDIFF(dd, 0, GetDate()) + 1, 0))

So, the final version, with a BETWEEN clause:

SELECT *

FROM MyTable

WHERE RequestDate BETWEEN

  DATEADD(dd, DATEDIFF(dd, 0, GetDate()), 0)

  AND DATEADD(ms, -3, DATEADD(dd, DATEDIFF(dd, 0, GetDate()) + 1, 0))

I cover all of this on my blog here:

http://musingmarc.blogspot.com/2006/07/more-on-dates-and-sql.html

# re: DateDiff in SQL Server

Wednesday, January 16, 2008 3:58 PM by Marc

DATEDIFF with 'day' will work fine across years, e.g:

select DATEDIFF(day, '1/15/07', '01/15/08 0:0:0')

returns 365

A much simpler solution is to use DATEDIFF with 'day', e.g.:

So to check if it's the current date use:

datediff(day, RequestDate, getdate()) = 0

No need to worry about indexes and math against columns, in my view. :)

# re: DateDiff in SQL Server

Thursday, January 24, 2008 12:42 AM by Daniel

This is all well and good but DateDiff will not work with something like this:

SELECT Recurring, Interval, Time, StartDate, DateDiff(Interval,StartDate,@BeginDate) AS MinRange

FROM Reminders

where Interval will either be 'd' or 'ww' or 'yyyy' etc.

Apparently:

'Interval' is not a recognized datediff option.

I can't even pass a parameter named interval either.

# re: DateDiff in SQL Server

Sunday, February 03, 2008 11:21 PM by Jessy

How about the date different but exclude saturday and sunday?

The leading UI suite for ASP.NET - Telerik radControls
Outstanding performance. Full ASP.NET AJAX support. Nearly codeless development.