THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Denis Gobo

Summer SQL Teaser #7 Datetime

First create this table

CREATE TABLE #DateMess (SomeDate datetime)
INSERT #DateMess VALUES('20070710')
INSERT #DateMess VALUES('20070711')
INSERT #DateMess VALUES('20070712')
INSERT #DateMess VALUES('20070713')

This should be easy for most people, but not everyone knows this.
Without running the query do you know how many rows you will get back from the query

FROM #DateMess
WHERE SomeDate <= '2007-07-12 23:59:59.999'

I created this teaser because of a response that Celko made here:  

Published Friday, July 13, 2007 9:08 AM by Denis Gobo
Filed under:



Scott R. said:

Shades of the "date / time rounding" or open-ended / close-ended range query issues!

The .999 seconds time component rounds up to the nearest 0.003 seconds (apparent time resolution within SQL Server datetime data type - may be different for smalldatetime) which is a value of 2007-07-13 00:00:00.000 or greater.  Thus, all 4 rows (including 7/13) show up in the query when you only wanted the first three rows (7/10, 7/11, 7/12).

A better (and less "busy" and less ambiguous) way to code the query is:


FROM #DateMess

WHERE SomeDate < '2007-07-13'

The date parameter in this query has an implied midnight (12:00 AM) time component, which can be coded or omitted as desired and does not appear to have a time component rounding value issue (al least if using implied midnight time component).  This query will yield the desired results of the first three rows (properly excluding 7/13).

I read the posts from the blog reference that Denis stated above.  While there are always many ways to achieve a given results, my preference for processing date ranges is to avoid use of the BETWEEN keyword or <= for an upper bound but instead to use a compound WHERE clause using >= and < as the following example:


Where SomeDate >= '... start date of exact desired start date - with implied or explicit time component ...'

And SomeDate < '... end date of one day past desired end date - with implied or explicit time component ...'

or more concrete ...

Where SomeDate >= '2007-07-10'

And SomeDate < '2007-07-13'

Just my preferences for proper function and readability - certainly not the only way it can be done.

FYI - these date / time lower and uipper bound issues become very important for specifying time-based table and index partitioning functions.  As an example, when partitioning by months, it is easier and less error-prone to specify start of months for date ranges using the >= / < approach described above than to try to use the >= / <= approach.  For example:

Partition #1: >= '2007-07-01' And < '2007-08-01'

Partition #2: >= '2007-08-01' And < '2007-09-01'

Partition #3: >= '2007-09-01' And < '2007-10-01'


This approach also simplifies not having to code in the varying last day of month (are there 28, 29, 30, or 31 days to this month?), which makes this solution easier to code (by hand or automated tools) and easier to understand (in my opinion).

The same is true for partitioning by years (just need to know 'yyyy-01-01').

How appropriate to publish this teaser and include test data for today - Friday the 13th!  Thanks again for the mental exercise - always a treat!

Scott R.

July 13, 2007 3:28 PM
New Comments to this post are disabled

About Denis Gobo

I was born in Croatia in 1970, when I was one I moved to Amsterdam (and yes Ajax is THE team in Holland) and finally in 1993 I came to the US. I have lived in New York City for a bunch of years and currently live in Princeton, New Jersey with my wife and 3 kids. I work for Dow Jones as a Database architect in the indexes department, one drawback: since our data goes back all the way to May 1896 I cannot use smalldates ;-( I have been working with SQL server since version 6.5 and compared to all the other bloggers here I am a n00b. Some of you might know me from or even from some of the newsgroups where I go by the name Denis the SQL Menace If you are a Tek-Tips user then you might know me by the name SQLDenis, I am one of the guys answering SQL Questions in the SQL Programming forum.

This Blog


Privacy Statement