# Careful When Trying To CAST DATETIME AS INT

Posted September 27, 2006 at 3:07 PM by Ben Nadel

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.

### Looking For a New Job?

25% of job board revenue is donated to Kiva. Loans that change lives - Find out more »

Sep 29, 2006 at 3:31 PM // reply »

Yikes - nice catch!

I've got to go fix my code.

Sep 29, 2006 at 3:48 PM // reply »

No worries dude. It's all about the team work.

Oct 12, 2006 at 10:39 AM // reply »

Do you know by how much it rounds up? like a minute, hour, day or what?

Oct 12, 2006 at 11:12 AM // reply »

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.

May 12, 2007 at 3:05 PM // reply »

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

May 30, 2007 at 4:27 PM // reply »

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)

May 30, 2007 at 4:31 PM // reply »

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)

Aug 3, 2007 at 9:24 AM // reply »

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)

Aug 30, 2007 at 5:07 PM // reply »

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 )

May 11, 2010 at 1:12 PM // reply »

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!

May 12, 2010 at 5:21 AM // reply »

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!

May 13, 2010 at 10:51 PM // reply »

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

Seems like everyone has a different approach to this problem.

Oct 25, 2010 at 11:43 AM // reply »

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

Nov 4, 2010 at 10:06 PM // reply »

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

Feb 14, 2011 at 3:06 PM // reply »

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

Mar 8, 2011 at 7:32 PM // reply »

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

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.

 Author Name: Author Email: Author Website: Comment: Supported HTML tags for formatting: bold   italic   code Remember my information Subscribe to comments Send me a copy of this comment
InVision App - Prototyping Made Beautiful With Prototyping Tools Recent Blog Comments
Mar 11, 2014 at 6:20 PM
Reading In File Data One Line At A Time Using ColdFusion's CFLoop Tag Or Java's LineNumberReader
Thanks Ben for you very interesting articles on handling large files. As I am not a programmer, I am quite insecure whether my issue is directly related to your description or not. My problem is, tha ... read »
Mar 11, 2014 at 12:21 PM
Compound Transclusion Prevented In AngularJS 1.2
Yeah this hit our team too and locked us as 1.1.5. It seemed like an irresponsible unilateral decision, but our graver concern is the future decisions the Angular team will make with other fundamenta ... read »
Mar 11, 2014 at 2:00 AM
ColdFusion, jQuery, And "AJAX" File Upload Demo
Mar 10, 2014 at 8:24 PM
Nested Views, Routing, And Deep Linking With AngularJS
@Steven, the action property is not something from AngularJS but rather part of the concept that Ben is describing in his blogpost here. it is a custom added property, which is read by the requestCo ... read »
Mar 10, 2014 at 2:03 PM
Nested Views, Routing, And Deep Linking With AngularJS
Where is the angular documentation for 'action:' it is not here: http://docs.angularjs.org/api/ngRoute/provider/ \$routeProvider Thanks. ... read »
Mar 10, 2014 at 12:06 PM
Using Track-By With ngRepeat In AngularJS 1.2
I was hoping that this will work with pagination using ng-repeat. My use case scenario is that I have an images object. images[0] = [im1,im2,im3] // First Page images[1] = [im4, im5, im6] // Second ... read »
Mar 9, 2014 at 6:11 PM
For Better Security Use HtmlEditFormat() In Conjunction With JSStringFormat() In ColdFusion
It looks like htmleditformat() will be deprecated in CF 11 https://wikidocs.adobe.com/wiki/display/coldfusionen/New +in+ColdFusion ... read »
Mar 9, 2014 at 10:55 AM
\$.stop() vs. \$.finish() In jQuery Animations
Nice feature! Thanks for sharing. :) Good for when you are making a 100% AJAX controlled site. ... read »