Ask Ben: Comparing Only Times in SQL
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:
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.
Want to use code from this post? Check out the license.
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.
A handy trick.
Thanks dude.
This actually doesn't work. I know it's a pretty bold statement, but when you cast it back to a DATETIME, it appends a "1900-01-01" date to the beginning.
I mean I can substring() the time out of the result, but I could have substring()ed out the time from GETDATE() to begin with.
hello Sir,
I need an one query that gives particular complaint number which has some fixed time duration like 6hrs within that it has to solved other wise that should be displayed as output..
Now my problem is i'm adding that 6hrs to the time at which complaint has registered and comparing it with the system time.. here's my query..
"select compno,compdate,comptime from urbrur where status='Under Process' and DATEADD(hh,6,comptime)<=GETDATE()"
but the output 'm getting is inconsistent . .
Compno Compdate comptime
16388 8/31/2011 10:11 AM
16790 9/15/2011 8:29 PM
16905 9/22/2011 5:05 PM
16906 9/22/2011 5:37 PM
17068 9/30/2011 5:24 PM
17085 10/1/2011 6:43 PM
17086 10/1/2011 6:48 PM
17087 10/1/2011 6:53 PM
this is my smaple data fetching it at the system time of 7:17 PM on october 1st.
so w.r.t this query and with this system time as i think the last 3complaints should not be displayed i'e, for 17085 6:43pm + 6hrs means 12:43am is not <= current system time 7:17 PM then that complaint nomber shouldn't be displayed..then kindly plz suggest me sir, i'm very much needed from last 10days . .
Sir, other thing is to say that the
compno-numeric;compdate-nvarchar(50);comptime-nvarchar(50);
And i'm using SqlServer2000,VS2008, ASP.Net with C#
Thanking You,
praveen
@Ben Nadel, I love the alternative to using string conversions, but seeing as it doesn't work for certain times as ExceptionalCase pointed out, I think you should take this down. The problem is float is not large enough to store times such as 14:30 so it's losing precision. I'm not sure how SQL calculates the float version of a DateTime so I don't know if it is a infinitely repeating decimal or just a large number (40032.6041666667). Interestingly if you convert this float value, with the 6 rounded up to 7, back to a datetime it yields the right time. But if it is not rounded to 7, it gives the wrong time.
If you want to work with the time portion of a datetime as an integer, this simple snip gives you minutes since midnight:
DATEDIFF(MINUTE, CAST(MyDate AS DATE), MyDate)
So, if I want to capture records that are only dated during the day, I use:
WHERE
DATEDIFF(MINUTE, CAST(MyDate AS DATE), MyDate)
BETWEEN 540 -- 9AM
AND 1020 -- 5PM
You could also work with seconds since midnight if you need that level of detail.