Ask Ben: Comparing Only Times in SQL
Posted July 11, 2006 at 9:07 AM
I like what you did with getting the date part of a SQL date/time stamp. But, I need to compare just the time part of two dates. How can I get the only the time part of a date/time stamp?
Comparing times in a SQL date/time stamp has always been a bit hairy. Over time, I have found it much easier to store time outside of a date/time stamp and instead, keep it in a 5 character text field in 24-hour time format (ex. 17:30). This way, I can keep dates and time separate. Of course, that's for more simple dates where I don't need to know seconds - something I might do for an event management system. This has it's benefits but it also has limitations that I won't go into now.
But, that aside, let's say you do have your time in a date/time stamp. Getting the time portion of the date/time stamp is very similar to how we got the date portion. The difference is that this time, we only care about the post-decimal numbers, not the pre-decimal numbers. So, in order to get the time, you have to subtract the "INT" of the date/time from the FLOAT cast of the date time:
Launch code in new window » Download code as text file »
- CAST(
- (
- -- The float cast will give us the full date
- -- representation as a number.
- CAST( h.date_created AS FLOAT ) -
-
- -- The floor/float cast will give us just the
- -- "date" of the date/time stamp.
- FLOOR( CAST( h.date_created AS FLOAT ) )
- )
- AS DATETIME
- )
We can't just cast the date to an INT because that will round the value. This can be confusing because in my experience, SQL does not always round when casting from FLOAT to INT... but going from DATETIME to INT, it does seem to round consistently. So that's why we do the FLOOR() of the FLOAT CAST. That equation will provide us with just the decimal part of the date/time stamp. Then, once cast back to DATETIME, you will have a date/time stamp with a constant date and the real time.
NOTE: Keep in mind that the "date" portion is still there! It's just that now, it's a constant value (whatever the zero date in the database is).
Now, before you go about casting stuff back to DATETIME, which is an extra processing step, think about what you are doing. If you are just comparing dates for greater/lesser quality, then you don't need to cast back to DATETIME since comparing the resultant decimal (from our cast-math) will yield the same outcome. Things such as selecting a record with the MAX() time or something along those lines will handle just fine using the numeric representation.
Needless to say, this is a lot of headache for a simple comparison. That is why I store my times separate when I need to utilize the time in any sort of complex fashion.
Download Code Snippet ZIP File
Post Comment | Ask Ben | Permalink | Other Searches | Print Page
Reader Comments
F'ing Beautiful man,
I'm now able to narrow down shift info or applying the time range to each day without number crunching the app side and on top of that it decreased the number of logical reads of my stored procedure.
You rock dude. Brilliant!
Where (BL.Occurred Between @StartDate and @EndDate)
--Compare Time for Each Day
and(
--Get Time Occured Subtract Date
CAST((Cast(Occurred AS FLOAT) - FLOOR(CAST(Occurred AS FLOAT))) AS Datetime)
Between
--Get Time Only from StartDate
(CAST((Cast(@StartDate AS FLOAT) - FLOOR(CAST(@StartDate AS FLOAT)))AS Datetime))
and
--Get Time Only from EndDate
(CAST((Cast(@EndDate AS FLOAT) - FLOOR(CAST(@EndDate AS FLOAT)))AS Datetime))
)
Glad that I could help :)
Boomy, thanks for this mate!
It doesn't work for some cases, try this example:
declare @endTime datetime, @selectDate datetime
set @selectDate = '2009-8-7'
set @endTime = '2009-8-9 14:30:00'
SET @endTime = CAST(cast(@selectDate as float) + (cast(@endTime as float) - FLOOR( CAST(@endTime AS FLOAT)))AS DATETIME)
It returns '2009-8-7 14:29:59.997' instead of '2009-8-7 14:30:00'
@ExceptionalCase,
Yeah, it looks like you are getting a rounding error with FLOAT precision. Not sure you can do anything about that.
