Converting datetime to integer and back

Hi all,

I have a problem converting datetime to integer (and than back to
datetime).
Depending whether the time is AM or PM, same date is converted to two
different integer representations, which holds as true on reversal
back to datetime.

AM Example:

declare @.DI integer; declare @.DD datetime
set @.DI = cast(cast('3/12/2003 11:34:02 AM' as datetime) as integer)
set @.DD = cast (@.DI as datetime)
print @.DI; print @.DD

Result:
37690
Mar 12 2003 12:00AM

PM Example:

declare @.DI integer; declare @.DD datetime
set @.DI = cast(cast('3/12/2003 11:34:02 PM' as datetime) as integer)
set @.DD = cast (@.DI as datetime)
print @.DI; print @.DD

Result:
37691
Mar 13 2003 12:00AM

Now, this is not a big problem if I knew that this is how it is
supposed to work. Is this how SQL Server is supposed to work?Nikola (nigel35@.hotmail.com) writes:
> AM Example:
> declare @.DI integer; declare @.DD datetime
> set @.DI = cast(cast('3/12/2003 11:34:02 AM' as datetime) as integer)
> set @.DD = cast (@.DI as datetime)
> print @.DI; print @.DD
> Result:
> 37690
> Mar 12 2003 12:00AM
> PM Example:
> declare @.DI integer; declare @.DD datetime
> set @.DI = cast(cast('3/12/2003 11:34:02 PM' as datetime) as integer)
> set @.DD = cast (@.DI as datetime)
> print @.DI; print @.DD
> Result:
> 37691
> Mar 13 2003 12:00AM
> Now, this is not a big problem if I knew that this is how it is
> supposed to work. Is this how SQL Server is supposed to work?

Apparently, SQL Server rounds to the nearest wholest int. I wouldn't
say this makes much sense to me.

Then again, I have to admit that I don't really see the point with
converting datetime values to integer.

In any case, the workaround should be simple, first chop of the
time portion.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||"Erland Sommarskog" <sommar@.algonet.se> wrote:
> Nikola (nigel35@.hotmail.com) writes:
> > AM Example:
> > declare @.DI integer; declare @.DD datetime
> > set @.DI = cast(cast('3/12/2003 11:34:02 AM' as datetime) as integer)
> > set @.DD = cast (@.DI as datetime)
> > print @.DI; print @.DD
> > Result:
> > 37690
> > Mar 12 2003 12:00AM
> > PM Example:
> > declare @.DI integer; declare @.DD datetime
> > set @.DI = cast(cast('3/12/2003 11:34:02 PM' as datetime) as integer)
> > set @.DD = cast (@.DI as datetime)
> > print @.DI; print @.DD
> > Result:
> > 37691
> > Mar 13 2003 12:00AM
> > Now, this is not a big problem if I knew that this is how it is
> > supposed to work. Is this how SQL Server is supposed to work?
> Apparently, SQL Server rounds to the nearest wholest int. I wouldn't
> say this makes much sense to me.
> Then again, I have to admit that I don't really see the point with
> converting datetime values to integer.
> In any case, the workaround should be simple, first chop of the
> time portion.

VB6 will allow a similar translation and it has the same problem: a real
number is returned where the fractional (i.e. right of the decimal point)
part represents the time. So, converting from datetime to an int carries a
hidden conversion that rounds to get the integer. Check this out (I used
money, although I assume float or real would suffice).

declare @.d datetime
declare @.n money

set @.d = '3/12/2003'
set @.n = convert(money, @.d)
print convert(varchar, @.n) + ' - ' + convert(varchar, @.d)

set @.d = '3/12/2003 11:34 AM'
set @.n = convert(money, @.d)
print convert(varchar, @.n) + ' - ' + convert(varchar, @.d)

set @.d = '3/12/2003 11:34 PM'
set @.n = convert(money, @.d)
print convert(varchar, @.n) + ' - ' + convert(varchar, @.d)

set @.d = '3/13/2003'
set @.n = convert(money, @.d)
print convert(varchar, @.n) + ' - ' + convert(varchar, @.d)

Craig|||Erland Sommarskog (sommar@.algonet.se) writes:
> Apparently, SQL Server rounds to the nearest wholest int. I wouldn't
> say this makes much sense to me.
> Then again, I have to admit that I don't really see the point with
> converting datetime values to integer.
> In any case, the workaround should be simple, first chop of the
> time portion.

Actually there is an even simpler workaround:

declare @.d datetime
declare @.i int

SELECT @.d = '20020202 11:59:00'
SELECT @.i = convert(float, @.d)
SELECT @.i

SELECT @.d = '20020202 12:01:00'
SELECT @.i = convert(float, @.d)
SELECT @.i

This works, because when convering from float to int, truncation occurs...

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.aspsqlsql