Skip to main content
Ben Nadel at InVision In Real Life (IRL) 2018 (Hollywood, CA) with: David Boon
Ben Nadel at InVision In Real Life (IRL) 2018 (Hollywood, CA) with: David Boon ( @evadnoob )

Careful When Trying To CAST DATETIME AS INT

By on
Tags:

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

15,640 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.

1 Comments

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

2 Comments

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)

2 Comments

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)

1 Comments

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)

1 Comments

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 )

1 Comments

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!

1 Comments

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!

1 Comments

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

15,640 Comments

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

1 Comments

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

1 Comments

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

I believe in love. I believe in compassion. I believe in human rights. I believe that we can afford to give more of these gifts to the world around us because it costs us nothing to be decent and kind and understanding. And, I want you to know that when you land on this site, you are accepted for who you are, no matter how you identify, what truths you live, or whatever kind of goofy shit makes you feel alive! Rock on with your bad self!
Ben Nadel