What is this Date format? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

What is this Date format?

Author

Topic

flamz
Starting Member

21 Posts

Posted - 2008-03-28 : 14:11:28

I'm using the Data Publishing Wizard to create a T-SQL script and it outputs dates the following way:

CAST(0x00009A5B00000000 AS DateTime)

Could someone tell me what format that number is in? ie. How can I generate this number from, say, a COleDateTime or another time struct?

tx

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-28 : 14:38:56

Do a
SELECT CAST(0x00009A5B00000000 AS BIGINT) to see what number it returns.
I would bet it is number of days passed since January 1, 1900.

E 12°55'05.25"
N 56°04'39.16"

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-03-28 : 14:40:10

That value is an 8 byte binary value.
sql server datetime values are actually stored as a pair of 4 byte ints.

The left half of that value when converted from binary(4) to int is the part that represents the date part of the datetime value. That int is the number of days from 0 (1900-01-01)

select cast(0x00009A5B00000000 as datetime) --2008-03-10 00:00:00.000
select convert(int, 0x00009A5B) --39515
select dateadd(day, 39515, 0) --2008-03-10 00:00:00.000

Be One with the Optimizer
TG

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-03-28 : 14:42:11