Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

SQLAndy

I'm Andy Warren, currently a SQL Server trainer with End to End Training. Over the past few years I've been a developer, DBA, and IT Director. I was one of the original founders of SQLServerCentral.com and helped grow that community from zero to about 300k members before deciding to move on to other ventures.

Subtle Errors When Using Dates-Part 2

It’s so easy to make make mistakes with dates. Here is another one I saw recently, this one used when doing some checks on some billable data that had been captured:

select * from sometable where somedate between ‘6/1/2011’ and ‘7/1/2011’

Nine times out of ten when you see that statement what they meant was:

select * from sometable where somedate >= ‘6/1/2011’ and < ‘7/1/2011’

It’s a difference of almost nothing in time, but it can be a huge difference in data. Dates get stored as numbers, with the date to the left of the decimal and the time to the right of the decimal. Many only want to store the date and will remove the time (no longer needed if you have access to the separate date and time data types), but even if you don’t specify a time what you’re storing includes a time of zero – midnight.

Imagine if you look at the data and see:

  1. 6/1/2011 00:00:00
  2. 6/2/2011 00:00:00
  3. 6:30/2011 00:00:00
  4. 7/1/2011 00:00:00
  5. 7/1/2011 02:00:00

The first query above that uses the between will return the first four records. The second query only returns three. It’s not much difference in time, but it can be a big difference when you care about being precise!  It’s worse than it appears because the first day of each month will get counted in two different periods if the the time is zero.

Mistakes like this go unnoticed for years because the logic that is applied may be wrong, but it’s applied consistently.  It usually gets noticed when someone writes a new report and does the date range correctly, and then a client asks why is there a difference.

I wouldn’t call it a beginner error I (thought it often is) as much as I’d call it an expectation error. It seems like between should do what we want in that situation. Such are the things that bugs are made of.

Comments

Posted by bilx10 on 18 July 2011

Doesn't SQL Server use midday as the zero time rather than midnight? I have had issues with dates between excel sheets where some cells were formated as text (excel uses midnight as the zero point and formating as text converts a date to the number) and SQL Server and had to adjust the import to subtract either a 1 or a 2 from the number before casting it as a date depending on the time of day the import was done. This came to light after users picked up that there was a two day difference between some dates in the original excel sheets and database reports.

Posted by richardd on 18 July 2011

The bigger problem with this query is the date format. It's not clear whether you want data for the 6th January, or for the whole of June. You should be using a culture-invariant "YMD" format for date literals:

somedate >= '20110601' and somedate < '20110701'

Posted by Bob Barrows on 20 July 2011

@bilx10 - No, midnight is the zero time. What gets stored is the amount of time after midnight (the unit of time stored depends on which datatype is being used). Jet (Access) is similar: it stores date/times as decimal numbers, with the whole number representing the number of days since the seed date, and the fractional portion representing the time of day (0 = midnight, .5 = noon)

@SQLAndy, setting aside the invalid syntax, "where somedate >= ‘6/1/2011’ and < ‘7/1/2011’", I have to question your "nine times out of ten" estimate. I find it hard to believe that so many would have that expectation, but maybe that's because I've been doing this so long.

Leave a Comment

Please register or log in to leave a comment.