Careful When Trying To CAST DATETIME AS INT
Posted September 27, 2006 at 3:07 PM by Ben Nadel
The other day, someone named Ben my post about converting DATETIME values in SQL to FLOATS and flooring them to get the date-only part of the date time. He then suggested that I just CAST directly to INT instead of FLOAT and then FLOOR. I was a little taken back by that - had I missed such an obvious method? After double checking the way casting to INT works, I remembered why I don't do it that way; when you cast to INT, it rounds. So, depending on what time of date the DATETIME stamp is in, the cast to INT will either round up or down. That will of course give you completely bunk conversions. The FLOAT / FLOOR is used to ensure the same date is used.
Reader Comments
Yikes - nice catch!
I've got to go fix my code.
No worries dude. It's all about the team work.
Do you know by how much it rounds up? like a minute, hour, day or what?
Days are left of the decimal point. Time is right of the decimal point. When casting to INT it rounds to the nearest whole number which is going to be the nearest whole day. If it rounds down it rounds to "today". If it rounds up, it rounds to "tomorrow". Both of those will have zero time... or in other words, be 12:00:00 AM.
Casting to INT will truncate - normally other language does that.
try this:
SELECT
CAST( 0.9999999999999 AS int ),
CAST( 0.0000000000000001 AS int )
both of them return 0
it is quite safe to cast datetime to int :)
Test this one and you will know!
select cast('11 Jan 2007 11:00pm' as datetime), cast(cast('11 Jan 2007 11:00pm' as datetime) as float), cast(cast('11 Jan 2007 11:00pm' as datetime) as int)
Sorry, I copy and past wrong text. here your go test the following lines and you will know the difference...
select cast('11 Jan 2007 4:00am' as datetime), cast(cast('11 Jan 2007 4:00am' as datetime) as float), cast(cast('11 Jan 2007 4:00am' as datetime) as int), cast(cast(cast('11 Jan 2007 4:00am' as datetime) as int) as datetime)
select cast('11 Jan 2007 11:00pm' as datetime), cast(cast('11 Jan 2007 11:00pm' as datetime) as float), cast(cast('11 Jan 2007 11:00pm' as datetime) as int), cast(cast(cast('11 Jan 2007 11:00pm' as datetime) as int) as datetime)
If you cast first to float, it works. Try:
select
cast('11 Jan 2007 4:00am' as datetime),
cast(cast('11 Jan 2007 4:00am' as datetime) as float),
cast(cast(cast('11 Jan 2007 4:00am' as datetime) as float) as int),
cast(cast('11 Jan 2007 4:00am' as datetime) as int),
cast(cast(cast(cast('11 Jan 2007 4:00am' as datetime) as float) as int) as datetime)
select
cast('11 Jan 2007 11:00pm' as datetime),
cast(cast('11 Jan 2007 11:00pm' as datetime) as float),
cast(cast(cast('11 Jan 2007 11:00pm' as datetime) as float) as int),
cast(cast('11 Jan 2007 11:00pm' as datetime) as int),
cast(cast(cast(cast('11 Jan 2007 11:00pm' as datetime) as float) as int) as datetime)
hmm... it doesn't truncate datetime, it rounds, I wonder if this is a bug?
try this and see...
SELECT
CAST( 0.9999999999999 AS int ),
CAST( 0.0000000000000001 AS int )
SELECT
CAST( cast(0.9999999999999 as datetime) AS int ),
CAST( cast(0.0000000000000001 as datetime) AS int )
It's been several years since you posted this, but it is still helpful!
I'm a novice SQL guy, and was trying to CAST a Datetime AS Integer to get the date portion ... and was running into the rounding issue, and was very confused. I googled and found your post, and it immediately showed me how to fix my query.
Thanks!
Just to echo what Jay says above... very handy, was also using int and wondering why I wasn't getting as many records as expected.
Cheers!
@Jay, @Martyn,
Glad to keep on providing value even years after the post :) If you want to see about 1000 different opinions / approaches to this problem, take a look at this post:
http://www.bennadel.com/blog/122-Getting-Only-the-Date-Part-of-a-Date-Time-Stamp-in-SQL-Server.htm
Seems like everyone has a different approach to this problem.
I like the cast lads on dates but I,ve run into this problem on dates and was going to see can your cast work.
Basically I want take a date and get the date to the various components of the date and time like so...
CONVERT(DATETIME, CONVERT(VARCHAR(13), GETDATE(), 120)) -- gives me hours
CONVERT(DATETIME, CONVERT(VARCHAR(16), GETDATE(), 120)) -- gives me minutes
CONVERT(DATETIME, CONVERT(VARCHAR(10), GETDATE(), 120)) -- gives me days.. ie date with no time
however the only one it fails on is hours
CONVERT(DATETIME, CONVERT(VARCHAR(13), GETDATE(), 120)) -- gives '2010-10-25 16' - yyyy-mm-dd hh' but when converting it to a date time it throws a wobblely. I can fix it by add a ':00' string to the end.
So my question is, can this be achieved using he float and subtracting the right number to get you the part of the date that you want
a bit mad I know...
@Micko,
It's been a while since I've actually had to cast date/times. I'm quite a bit rusty on this one, sorry. Have you tried looking at some of the date/time functions for the database engine. I know they are slower, but they might just be easier.
Thanks for posting this.My boss was cracking his brain on this one.Took a simple google search to find you page and find a solution to dropping time stamp on date...
Thanks
Not sure of the performance ramifications of the following, but I came across some code that was doing this. At first I thought it was wrong until I realized sql was implicitly converting the PayEndDate to an integer. After some testing it appears to work consistently. Apparently DATEDIFF does the float/floor for you behind the scenes when using the day argument.
WHERE PayEndDate >= DATEDIFF(dd, 0, GETDATE())
So, to get the floored int conversion...
SELECT DATEDIFF(dd, 0, GETDATE())
and back again...
SELECT CAST(DATEDIFF(dd, 0, GETDATE()) AS DATETIME)
I have always used the convert to varchar and cast as date to strip out time, but I think I like the above better (until someone tells me it's a worse pig than the varchar convert ;)
