Ben Nadel
On User Experience (UX) Design, JavaScript, ColdFusion, Node.js, Life, and Love.
I am the chief technical officer at InVision App, Inc - a prototyping and collaboration platform for designers, built by designers. I also rock out in JavaScript and ColdFusion 24x7.
Meanwhile on Twitter
Loading latest tweet...
Ben Nadel at Scotch On The Rocks (SOTR) 2011 (Edinburgh) with:

Careful When Trying To CAST DATETIME AS INT

By Ben Nadel on
Tags: SQL

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.

Tweet This Groovy post by @BenNadel - Careful When Trying To CAST DATETIME AS INT Thanks my man — you rock the party that rocks the body!


Reader Comments

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.

Reply to this Comment

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 :)

Reply to this Comment

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)

Reply to this Comment

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)

Reply to this Comment

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)

Reply to this Comment

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 )

Reply to this Comment

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!

Reply to this Comment

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!

Reply to this Comment

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...

Reply to this Comment

@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.

Reply to this Comment

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

Reply to this Comment

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 ;)

Reply to this Comment

Post A Comment

You — Get Out Of My Dreams, Get Into My Comments
Live in the Now
Oops!
Comment Etiquette: Please do not post spam. Please keep the comments on-topic. Please do not post unrelated questions or large chunks of code. And, above all, please be nice to each other - we're trying to have a good conversation here.