How is date conversion being done in sql server

Question

I have a query like the one described below.

select CAST(0x83360B00 AS Date)

When I run this query in SQL server, I am able to get result in date format as:

2012-12-15

All I want to know is how this is being generated. Thanks in advance.

score 6 · Accepted Answer · answered May 02 '14 at 05:59

6

Sql server stores the internal representation of the DATE data type as the big endian count of days from 1 Jan 0001.

So 0x83360B00 is

SELECT 0x83 + 0x36 * 256 + 0x0B * 65536
= 734851 days

Add these back:

DECLARE @Date DATE = '1 Jan 0001'
SELECT DATEADD(dd, 734851, @Date)

Which returns:

2012-12-15

answered May 02 '14 at 05:59

StuartLC

Although as a human, I much prefer `2012-12-15` to `0x83360B00` :). I guess the [Gregorian calendar](http://en.wikipedia.org/wiki/Gregorian_calendar) doesn't make sense prior to 1600 or so. – StuartLC May 02 '14 at 07:14

score 0 · Answer 2 · answered May 02 '14 at 05:58

0

Probably with the corresponding cast to varbinary:

SELECT CAST(CAST('2012-12-15' AS DATE) AS VARBINARY)

answered May 02 '14 at 05:58

dean

i get that. its giving the varbinary to date conversion... right? – vstandsforvinay May 02 '14 at 06:04

2 Answers2