Convert datetimes to integers when doing date-only range searches in SQL.
From comment: 12/22/09
I discovered later on, this is actually a very BAD implementation. The reason is when using indexes, executing a function on each item requires an entire index scan to be done instead of an index seek. This completely screws up doing a range search for dates on a clustered index. Yikes.
The methods above work, but boy or they ever inefficient. Avoid them if at all possible. Consider using things like DateDiff(), etc.
And Remember, when your writing high performance queries always study the execution plan and reduce bottlenecks!
Why?
Sooner or later you will need to do date-only range searches (where you are not concerned with times). Many programmers will actually specify a start time of 12:00 AM and ending time of 11:59:59 PM to get around this. But if you write a lot of queries you will quickly find this to be an annoyance and without an appropriate solution it can be easy to forget about times all together; creating a hard to find bug that will crop up and have your users up in arms.
How?
Since datetimes are stored as binary values in SQL we can utilize this to our advantage by casting the datetime object to a decimal and passing that result to the floor() function to isolate the date and effectively chop off the time.
declare @startdate datetime
declare @enddate datetime
set @startdate = '01/01/2008'
set @enddate = '03/01/2008'
select
fname + ' ' + lname as emp_name,
convert(varchar(10), hire_date, 101) as hire_date,
hire_date as og_hire_date,
floor(cast(hire_date as decimal(9,4))) as og_hire_date_cast
from employees
where
floor(cast(hire_date as decimal(9,4))) between @startdate and @enddate
Note:
Conversely if we only wanted the time, we are concerned with what is on the right side of the decimal. Using the function makes this a cinch. For more information on how SQL stores datetimes internally, check out Date and Time (Transact-SQL) on MSDN
SQL Function, MSDN Ref.
CONVERT ( data_type [ ( length ) ] , expression [ , style ] )
CAST ( expression AS data_type [ (length ) ])
FLOOR ( numeric_expression )
No related posts.
Id recommend doing it like this instead as it avoids the need to call floor and is a wee bit faster - and a little more readable (imo)
CONVERT(datetime, CONVERT(int, CONVERT(float, the_date)))
or if you prefer using CAST
CAST(CAST(CAST(the_date AS float) AS int) AS datetime)
also, if you want your times to round up and down - i.e. anything after 12PM be the next day, its simpler - you just omit the first conversion to float
CONVERT(datetime, CONVERT(int, the_date))
OR
CAST(CAST(the_date AS int) AS datetime)
@Heath Provost
Is there any real reason to convert it from an int back to a datetime within the query? … Also, do you know off hand how to look at only the times (the right side of the decimal)? I can think of a few, but none I would want to mess with.
No. If your doing numeric comparisons, you don’t need to do the last conversion back to datetime, but it’s nearly free so I generally do it anyway just so when I read it later it is obvious what I meant to do.
As for handling time only data, The best way I know of doing it is like this:
CONVERT(datetime, the_date - CONVERT(int, CONVERT(float, the_date)))
or if you prefer using floor
CONVERT(datetime, the_date - FLOOR(CONVERT(float, the_date)))
and of course you could do this with CAST as well.
It doesn’t really matter which way you do it - but I have read in a few places that floor requires a wee bit more work internally. In reality it probably doesn’t matter enough to care about.
Note: conversions from float to int are done using a truncation, which is internally just lopping off the decimal part and is functionally equivalent to a call to floor. Conversions from datetime to int, however, are done using a rounding operation - hence the need to convert to float first if you dont want times after 12PM to round to the next day.
I discovered later on, this is actually a very BAD implementation. The reason is when using indexes, executing a function on each item requires an entire index scan to be done instead of an index seek. This completely screws up doing a range search for dates on a clustered index. Yikes.
The methods above work, but boy or they ever inefficient. Avoid them if at all possible. Consider using things like DateDiff(), etc.
And Remember, when your writing high performance queries always study the execution plan and reduce bottlenecks!