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

Convert field

I am having a bit of a problem. I have a date field that is comming from
a different platform as a float. I need to convert these records to
datetime or small datetime. Is their a Stored Procedure to do this in SQL
or what is the quickest way to do this?
Thanks>> I have a date field [sic] that is comming from a different platform as a floa
t. <<
You don't know that a column is not anything like a field!
You don't know that a row is not anything like a record!
You might be able to do a CAST(foobar AS DATETIME). the quickest way
is to keep temporal data in temproal columns.|||You need to find out the starting point, for inhstance, what does 0.5
mean? Noon of what day?
Suppose 0 means midningt of January 1st, 2000.
Use dateadd function to add days (the integer part of the float) to
that start date
Then use dateadd to add seconds|||I have to suggestions.
1. The code which is performing the transformation from the other platform
may be able to convert as it goes.
2. You can Cast the float to a datetime list this...
Declare @.Mydate Float
Set @.Mydate = Cast( 1.125 as Float )
Select Cast( @.Mydate as DateTime)
However, it will require that the float from the other platform is using 0
as the same Epoch of 1900-01-01 00:00:00.000 .
Regards
Colin Dawson
www.cjdawson.com
"JimS" <noholycowsspam@.ya_NoJunk_hoo.com> wrote in message
news:O1Al2EOXGHA.4924@.TK2MSFTNGP05.phx.gbl...
> I am having a bit of a problem. I have a date field that is comming from
> a different platform as a float. I need to convert these records to
> datetime or small datetime. Is their a Stored Procedure to do this in SQL
> or what is the quickest way to do this?
> Thanks
>