468,309 Members | 1,081 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,309 developers. It's quick & easy.

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?
Jul 20 '05 #1
3 118357
Nikola (ni*****@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, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #2
"Erland Sommarskog" <so****@algonet.se> wrote:
Nikola (ni*****@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
Jul 20 '05 #3
Erland Sommarskog (so****@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, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by js | last post: by
1 post views Thread by John Dann | last post: by
2 posts views Thread by =?Utf-8?B?bWF2cmlja18xMDE=?= | last post: by
reply views Thread by NPC403 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.