convert a datetime

If I issue:
SELECT CAST(CAST('2000-05-08 12:36:29.998' AS datetime) AS bigint) AS
Expr1
I get 36653 as the result.
If I issue:
SELECT CAST(CAST('2000-05-08 12:35:29.998' AS datetime) AS bigint) AS
Expr1
I get the same result even though the times differ by one minute.
Can anyone explain what is going on here and how I get unique integral
values from a conversion of a datetime value?
Many thanks.Hi, Andrew
If you convert a datetime to a number, you get the number of days
elapsed from Jan 1, 1900, with a fractional portion representing the
time. For example, you will get different results from these queries:
SELECT CAST(CAST('2000-05-08 12:36:29.998' AS datetime) AS float)
SELECT CAST(CAST('2000-05-08 12:35:29.998' AS datetime) AS float)
You will also get different results if you do this:
SELECT CAST(CAST(CAST('2000-05-08 12:36:29.998' AS datetime) AS
binary(8)) AS bigint)
SELECT CAST(CAST(CAST('2000-05-08 12:35:29.998' AS datetime) AS
binary(8)) AS bigint)
For an explanation, here is a quote from Books Online:
Values with the datetime data type are stored internally by
Microsoft SQL Server as two 4-byte integers. The first 4 bytes store
the number of days before or after the base date, January 1, 1900.
The base date is the system reference date. Values for datetime
earlier than January 1, 1753, are not permitted. The other 4 bytes
store the time of day represented as the number of milliseconds
after midnight.
The smalldatetime data type stores dates and times of day with less
precision than datetime. SQL Server stores smalldatetime values as
two 2-byte integers. The first 2 bytes store the number of days
after January 1, 1900. The other 2 bytes store the number of minutes
since midnight. Dates range from January 1, 1900, through
June 6, 2079, with accuracy to the minute.
However, you should not rely on a physical implementation of a data
type. Why do you want a number instead of a datetime value ?
Razvan|||
Razvan Socol wrote:

>For an explanation, here is a quote from Books Online:
> Values with the datetime data type are stored internally by
> Microsoft SQL Server as two 4-byte integers. The first 4 bytes store
> the number of days before or after the base date, January 1, 1900.
> The base date is the system reference date. Values for datetime
> earlier than January 1, 1753, are not permitted. The other 4 bytes
> store the time of day represented as the number of milliseconds
> after midnight.
>
>
Just for the record, this documentation is incorrect. The "other 4
bytes" represent
the number of 1/300-second intervals after midnight, not the number of
milliseconds.
Steve Kass
Drew University

>Razvan
>
>|||Thanks for the detailed explanation. I had read the BO section but assumed
cast/convert would account for both bytes when converting to bigint.
However, it obviously ignores the lower byte.
My objective is to subtract two datetimes in order to report an elapsed
time. Is there a better way to do this?
Regards,
Andrew
"Razvan Socol" <rsocol@.gmail.com> wrote in message
news:1133679477.157752.260590@.g14g2000cwa.googlegroups.com...
> Hi, Andrew
> If you convert a datetime to a number, you get the number of days
> elapsed from Jan 1, 1900, with a fractional portion representing the
> time. For example, you will get different results from these queries:
> SELECT CAST(CAST('2000-05-08 12:36:29.998' AS datetime) AS float)
> SELECT CAST(CAST('2000-05-08 12:35:29.998' AS datetime) AS float)
> You will also get different results if you do this:
> SELECT CAST(CAST(CAST('2000-05-08 12:36:29.998' AS datetime) AS
> binary(8)) AS bigint)
> SELECT CAST(CAST(CAST('2000-05-08 12:35:29.998' AS datetime) AS
> binary(8)) AS bigint)
> For an explanation, here is a quote from Books Online:
> Values with the datetime data type are stored internally by
> Microsoft SQL Server as two 4-byte integers. The first 4 bytes store
> the number of days before or after the base date, January 1, 1900.
> The base date is the system reference date. Values for datetime
> earlier than January 1, 1753, are not permitted. The other 4 bytes
> store the time of day represented as the number of milliseconds
> after midnight.
> The smalldatetime data type stores dates and times of day with less
> precision than datetime. SQL Server stores smalldatetime values as
> two 2-byte integers. The first 2 bytes store the number of days
> after January 1, 1900. The other 2 bytes store the number of minutes
> since midnight. Dates range from January 1, 1900, through
> June 6, 2079, with accuracy to the minute.
> However, you should not rely on a physical implementation of a data
> type. Why do you want a number instead of a datetime value ?
> Razvan
>|||> My objective is to subtract two datetimes in order to report an elapsed
> time. Is there a better way to do this?
Try DATEDIFF. You can specify a datepart appropriate for the resolution you
need.
SELECT DATEDIFF(ms, '20000508 12:35:29.998', '20000508 12:36:29.998') AS
ElapsedMilliseconds
Hope this helps.
Dan Guzman
SQL Server MVP
"Andrew Chalk" <achalk@.magnacartasoftware.com> wrote in message
news:%235KvzjO%23FHA.1168@.TK2MSFTNGP10.phx.gbl...
> Thanks for the detailed explanation. I had read the BO section but assumed
> cast/convert would account for both bytes when converting to bigint.
> However, it obviously ignores the lower byte.
> My objective is to subtract two datetimes in order to report an elapsed
> time. Is there a better way to do this?
> Regards,
> Andrew
> "Razvan Socol" <rsocol@.gmail.com> wrote in message
> news:1133679477.157752.260590@.g14g2000cwa.googlegroups.com...
>|||Exactly what I needed. Many thanks!
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:O90$$5P%23FHA.2832@.TK2MSFTNGP14.phx.gbl...
> Try DATEDIFF. You can specify a datepart appropriate for the resolution
> you need.
> SELECT DATEDIFF(ms, '20000508 12:35:29.998', '20000508 12:36:29.998') AS
> ElapsedMilliseconds
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Andrew Chalk" <achalk@.magnacartasoftware.com> wrote in message
> news:%235KvzjO%23FHA.1168@.TK2MSFTNGP10.phx.gbl...
>|||Hi Andrew,
While converting datetime to numeric value, time part is returned in decimal
values. Try returning the value in Float instead of BigInt as below
SELECT CAST(CAST('2000-05-08 12:36:29.998' AS datetime) AS float) AS Exp
r1
--36652.52534718364
SELECT CAST(CAST('2000-05-08 12:37:29.998' AS datetime) AS float) AS Exp
r1
--36652.526041628087
Ok.
Deepak Sant
"Andrew Chalk" wrote:

> If I issue:
> SELECT CAST(CAST('2000-05-08 12:36:29.998' AS datetime) AS bigint) AS
> Expr1
> I get 36653 as the result.
> If I issue:
> SELECT CAST(CAST('2000-05-08 12:35:29.998' AS datetime) AS bigint) AS
> Expr1
> I get the same result even though the times differ by one minute.
> Can anyone explain what is going on here and how I get unique integral
> values from a conversion of a datetime value?
> Many thanks.
>
>|||Thats wacky, because this will lead to the misunderstandable values,
due to the imprecision of datetime:
SELECT CAST(CAST('2000-05-08 12:36:29.995' AS datetime) AS float)
AS Expr1
--36652.52534718364
SELECT CAST(CAST('2000-05-08 12:36:29.996' AS datetime) AS float)
AS Expr1
--36652.52534718364
SELECT CAST(CAST('2000-05-08 12:36:29.997' AS datetime) AS float)
AS Expr1
--36652.52534718364
SELECT CAST(CAST('2000-05-08 12:36:29.998' AS datetime) AS float)
AS Expr1
--36652.52534718364
So you gotta be careful,
HTH, Jens Suessmeyer.|||Hi Andrew,
While converting datetime to numeric if time part is included it always
returns in decimal values, so try for Float instead of Bigint datatype
SELECT CAST(CAST('2000-05-08 12:36:29.998' AS datetime) AS float) AS Exp
r1
SELECT CAST(CAST('2000-05-08 12:37:29.998' AS datetime) AS float) AS Exp
r1
Deepak Sant
"Andrew Chalk" wrote:

> If I issue:
> SELECT CAST(CAST('2000-05-08 12:36:29.998' AS datetime) AS bigint) AS
> Expr1
> I get 36653 as the result.
> If I issue:
> SELECT CAST(CAST('2000-05-08 12:35:29.998' AS datetime) AS bigint) AS
> Expr1
> I get the same result even though the times differ by one minute.
> Can anyone explain what is going on here and how I get unique integral
> values from a conversion of a datetime value?
> Many thanks.
>
>