ravikaliappan - Wednesday, August 9, 2017 3:45 AM

ravikaliappan - Wednesday, August 9, 2017 3:37 AM

Eirikur Eiriksson - Saturday, August 2, 2014 12:38 PM

Jeff Moden (8/2/2014)

---

Sean Lange (7/31/2014)

---

Sql server stores datetime as decimal. You can easily cast a datetime to decimal. But what is the point? What are you trying to do with it?

I use to think that until I saw a proof and tried it all myself. If you look at the DATETIME data type in 2005 BOL, it states...

Values with the datetime data type are stored internally by the Microsoft SQL Server 2005 Database Engine 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. 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. The Database Engine 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.

Here's a good link that demonstrates... http://blogs.lessthandot.com/index.php/datamgmt/datadesign/how-are-dates-stored-in-sql-server/ The bad part is that, as of 2008, they no longer tell you how the date/time is stored. Shifting gears a bit, I love DATETIME instead of DATETIME2. You can convert DATETIME into a DECIMAL or FLOAT number, etc, as expected and, as you stated, the number to the left of the decimal place is the number of whole days since the first of January 1900 and every thing to the right of the decimal is fractional days which can easily be manipulated as time. You cannot do such conversions with the "new" DATETIME2 data type, which is really disappointing.

Doesn't look like the format has changed, look at this query DECLARE @DT2_0 DATETIME2(0) = '1900-01-01 00:00:00' DECLARE @DT2_1 DATETIME2(1) = '1900-01-01 00:00:00.1' DECLARE @DT2_7 DATETIME2(7) = '1900-01-01 00:00:00.0000001' DECLARE @dt DATETIME = '1900-01-01 00:00:00.003' SELECT CONVERT(VARBINARY(12),@DT2_0,0) UNION ALL SELECT CONVERT(VARBINARY(12),@DT2_1,0) UNION ALL SELECT CONVERT(VARBINARY(12),@DT2_7,0) UNION ALL SELECT CONVERT(VARBINARY(12),@DT,0) Results 0x000000005B950A 0x010100005B950A 0x0701000000005B950A 0x0000000000000001 The datetime2 format is first byte is precision, last two are the number of days since year 1 and the middle holds the number of time units since midnight depending on the precision. Mind you, one has to reverse the bytes as it is small endian 😉 The datetime format is still the same upto and including 2014. 😎

How to convert VarBinary to DateTime2

How to conver varbinary to date with time?