Using DateDiff with SQL Server

Working with DateDiff In SQL Server 

This article is going to discuss the SQL Server DateDiff function and how to use them to find records that fall within a certain date range.

I get lots of questions about dates in SQL Server.  One common type of question centers around trying to find dates that fall within a date range. 

These questions usually stem from a lack of understanding about how SQL Server actually stores DateTime data.  

Data Types

To take a quick step back, there are actually 7 date/time focused data types that Microsoft has implemented in SQL Server 2017.  Those can be found here.

DateTime is deprecated as of 2008 and current and future work should use DateTime2. 

DateTime2  is described here .  It has a enormous range going from January 1,  0001  (1 CE) to December 31, 9999.    Dates coming in BCE will be discussed in a future article.

SmallDateTime is described here. Its range is quite a bit smaller. It ranges from 1/1/1900 to 06/06/2079.   While this range is probably enough for most applications, I encourage you to think positive,  assume your work is going to be around for another 60 years and choose datetime2 instead. 

Datetime2 uses between 6 and 8 bytes of storage while SmallDateTime uses 4 bytes. I don't consider this difference in storage to be significant, but your data model and hardware requirements may lead you to a different conclusion.

With  Datetime, Datetime2, and SmallDateTime, the date component is stored on the left of the decimal while the time component is stored to the right of the decimal.

My query won't return records in a date range... 

So one of the common questions I get is that the following query does not return the expected values 

Select * from RFQ where RequestDate  =  '4/5/2018'

"Greg, " they say. I know for a fact that we had  RFQs come in yesterday.  Look, I can order them by RequestDate

Id CompanyName RequestDate                         RequestDescription

1 Able Enterprises 2018-02-25 12:25:32.077 Please design a widget

2 Baker Enterprises 2018-03-10 12:25:32.077 Please manufacture 100 dongles

3 Charter Enterprises 2018-03-23 12:25:32.077 Please build a mouse trap

4 Drake Enterprises 2018-04-05 12:25:32.077 We need a flying boat

The reason is very simple.  The request for the flying boat did not come in at midnight, so the RequestDate value is not EQUAL to '4/5/2018'

 Usually the query is expressed in terms of a date range, but you get the idea. 

DateDiff to the rescue

The DateDiff function was written to handle exactly this problem.  The full documentation for the DateDiff function can be found here.  In essence, the date diff function serves to help you do Add/Subtract math on dates. You have a variety of units of time that you can concern yourself 

The syntax for a call to DateDiff is this 

                     DateDiff(  datepart ,  startDate, endDate ) 

For example 

 

                    select   DateDiff( d ,  '01/01/2018', '01/31/2018')  as DayDiff

The datepart parameter is a bit odd. It looks like a string , but you do not enclose it in single ticks.

As you can see, they pretty much have this one covered. 

 

year	yy, yyyy
quarter	qq, q
month	mm, m
dayofyear	dy, y
day	dd, d
week	wk, ww
hour	hh
minute	mi, n
second	ss, s
millisecond	ms
microsecond	mcs
nanosecond	ns

The second and  third parameters have to be valid dates.  They can either be variables, or they can be string constants that represent dates, as in my example above. 

The best way to keep things straight in your head is to remember that if the first date comes before the second date, then the resulting value will be positive.  

Conversely, if the first date comes after the second date , then the result will be negative. 

Returning to our example 

                    select   DateDiff( d ,  '01/01/2018', '01/31/2018')  as DayDiff

will yield the number 30. While 

                    select   DateDiff( d ,  '01/01/2018', '01/31/2018')  as DayDiff

will yield the number -30. 

Why 30 and not 31?   DateDiff counts the boundaries that are crossed.   Since we have asked for days (d) , the function will consider moving past midnight as 1 day. If we were to change the query so the second date included a time of 5pm, it would still return a 30 because we have not increased the number of midnights that transpire between the two bookmark times. 

                    select   DateDiff( d ,  '01/01/2018', '01/31/2018 17:00:00')  as DayDiff

Probably a better way to demonstrate this is to look at some examples with year as the unit of time 

NOTE: Don't fall for the mistake of thinking 'y' indicates year. It indicates Julian Date. 

                    select DateDiff(yy, '12/30/2018', '12/31/2018')   --returns 0 because they are both in the same year

                    select DateDiff(yy, '12/31/2018', '1/1/2019')  -- returns 1 because we have crossed a new year

                    select DateDiff(yy, '12/31/2018', '12/31/2019')  -- returns 1. We have only crossed 1 new year

Datediff'ing seems like a trivial problem that you could handle on your own , but its deceiving. Datediff removes all the complexities relatted to  the different lengths of months and leap years. 

Records falling in a Date Range

So back to our task of finding RFQs that happened in a date range, lets look for dates that happened in March. 

 Declare  @from datetime2  = '3/1/2018'

 Declare @to  datetime2 = '3/31/2018'

select * from RFQ where 

                                          datediff( d, @from, RFQDate ) >=0 

                                      and datediff( d,  RFQDate , @To ) >=0 

This will return the desired rowset 

Id CompanyName RequestDate                         RequestDescription

2 Baker Enterprises 2018-03-10 12:25:32.077 Please manufacture 100 dongles

3 Charter Enterprises 2018-03-23 12:25:32.077 Please build a mouse trap

 

There are multiple ways you can express the filter.  You could keep the RFQDate field as the third parameter in both calls, and just switch the sign on the second one.  

select * from RFQ where 

                                          datediff( d, @from, RFQDate ) >=0 

                                      and datediff( d,  @To , RFQDate   ) <=0   -- syntax changes highlighted

I prefer the first style but really offer nothing beyond preference to suggest it is fundamentally better. I would suggest that you and your team select one style and remain consistent.  Clearly there is room here for silly mistakes, either in typing or assuming one style when looking at another. 

The end dates can be inclusive by keeping the equal sign  in the comparison ( <=,  >= )  or exclusive by dropping the equal sign from the comparison  ( <  , >  )

--show me March RFQs that did not fall on Month Boundaries

select * from RFQ where 

                                          datediff( d, @from, RFQDate ) > 0 

                                      and datediff( d,  RFQDate , @To ) > 0 

Between 

Between is a SQL keyword that seems to accomplish the same thing. Lets try it out

--Show me March RFQs

select * from rfq  where requestdate between '3/1/2018' and '3/31/2018'

Here you will get the rowset you would expect from our overly simple example. The date range brackets our result set, we are lulled into thinking we did good... 

Id CompanyName RequestDate                         RequestDescription

2 Baker Enterprises 2018-03-10 12:25:32.077 Please manufacture 100 dongles

3 Charter Enterprises 2018-03-23 12:25:32.077 Please build a mouse trap

 

But tighten up the query and ask for dates that are included in our data set...

--I can't remember that datediff syntax, can you just show me March RFQs please 

select * from rfq  where requestdate between '3/10/2019' and '3/23/2019'

Id CompanyName RequestDate                         RequestDescription

2 Baker Enterprises 2018-03-10 12:25:32.077 Please manufacture 100 dongles

Now you have set yourself up for the same problem we had simply using the EQUAL comparison. 

In fact, if you read the doc on BETWEEN you will see that it is implemented by using <= and >=  with the 2 parameters. 

So  the 3/10/2018 row is returned because it is greater than 3/10/2018 at midnight, but the 3/23/2018 row is not returned because it is greater than 3/23/2018 at midnight. 

 

Conclusion

Datediff is a great function that really, really should be part of your go to toolbox of functions.  You need to be able to do this in your sleep. 

By using DateDiff consistently, you will never have to worry about silly boundary cases where you included or excluded rows by mistake.

Thanks for reading. Let me know what you think.