Careful When Trying To CAST DATETIME AS INT
Posted September 27, 2006 at 3:07 PM
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.
Comments (9) | Post Comment | Ask Ben | Permalink | Other Searches | Print Page
Creating A ColdFusion CFSetting Tag With Open / Close Tags
Skin Spider : Basic Site Structure Completed
Reader Comments
Yikes - nice catch!
I've got to go fix my code.
Posted by (Another) Ben on Sep 29, 2006 at 3:31 PM
No worries dude. It's all about the team work.
Posted by Ben Nadel on Sep 29, 2006 at 3:48 PM
Do you know by how much it rounds up? like a minute, hour, day or what?
Posted by Wfhanna1 on Oct 12, 2006 at 10:39 AM
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.
Posted by Ben Nadel on Oct 12, 2006 at 11:12 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 :)
Posted by nant on May 12, 2007 at 3:05 PM
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)
Posted by Curious on May 30, 2007 at 4:27 PM
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)
Posted by Curious on May 30, 2007 at 4:31 PM
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)
Posted by Richard on Aug 3, 2007 at 9:24 AM
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 )
Posted by anony-mouse on Aug 30, 2007 at 5:07 PM
