Datetime int float, why we can go to float but not to int

Question

When casting a Datetime to a float you get the offset as a float. Converting that to a int only gives part of the date. So far so good, we have a trimmed date when we go back to datetime

Doing the same and converting directly to int and back to datetime only works some of the time.

Mainly for use in sql server

Code:

CONVERT(datetime,floor(CONVERT(float,START_TIME_1)))

Not working:

CONVERT(datetime,CONVERT(Int,START_TIME_1))

Anyone know why this is?

I was given the answer to this question by "Mikael Eriksson" -> "That is because when converting from datetime to int the value is rounded to the nearest int. Datetime values with a time part equal or greater than 12:00 will be rounded up to the next integer/day."

Obviously this is by now an outdated question due to support of the cast and convert function now being everywhere. Not to mention that datetime2 should be used.

Answer 1

To remove the time part in SQL Server 2005 you can use this:

select dateadd(day, datediff(day, 0, getdate()), 0)

From SQL Server 2008 you can use the date datatype instead.

select cast(getdate() as date)

Casting to float and using the floor function also works as you have seen but I think the above alternatives is to prefer.

Your question was why a cast to integer sometimes return the wrong value.

That is because when converting from datetime to int the value is rounded to the nearest int. Datetime values with a time part equal or greater than 12:00 will be rounded up to the next integer/day.

The behaviour is documented in CAST and CONVERT (Transact-SQL) in the section "Truncating and Rounding Results".