When you follow online communities dedicated to SQL Server with open eyes, you certainly notice a whole lot of questions are closely related to the DATETIME datatype in one way or the other. Obviously, working with DATETIME not as easy as it may seem.
To me this seems kind of strange. My believe is that working with DATETIME is actually easy. There is nothing magic about the datatype itself. And there is no magical or esoterical date calculus involved when doing date arithmetic. One only needs to understand some basic concepts of how the DATETIME datatype works in order to deal safely with temporal data. The purpose of this article is to help the reader gain an understanding of this interesting part of SQL Server, and to set some facts clear about this datatype.
Throughout this article, you’ll notice that I use the ISO dateformat: yyyymmdd. This is a safe dateformat, meaning it should always work, no matter what your specific computer settings are. It is also unaffected by any SET DATEFORMAT or SET LANGUAGE settings. Even if you do not develop databases and applications for international use, you should really get into the habit to use a safe date format. Only two of SQL Server’s many possible date formats are considered safe, that are SQL Server’s date style 112 and 126. The first one being the ISO format, while the latter is the ISO8601 format. They are mentioned with an example in BOL when you look at CAST and CONVERT. The sooner you get used to these styles, the better, and many potential problems will never even arise.
You’ll also note that I deliberately rely frequently on implicit conversion from CHAR to DATETIME. Well, while implicit conversion is not always something you should adapt as good development best practice, I consider it safe according to SQL Server’s datatype precedence for DATETIME conversion, and after all, that’s the only thing we will discuss here.
First, in this article, we will take a look at some interesting internals of the DATETIME datatype, move then on to some general guidelines for querying DATETIMEs, and finally finish with some tips, tricks, and possible solutions to questions that are frequently asked in SQL Server forums.
Please note that all code samples are written for SQL Server 2000. I believe they will also work also in previous version. Right now, it is too early to say a thing about the next version of SQL Server. Once it is RTM, I will review this article again and make modification and/or additions where appropriate.
Enough foreword, let’s get started.
DATETIME Data is Stored in a Readable Format
Sure!
SELECT CAST(GETDATE() AS BINARY(8)) AS WhatIsReallyStored
WhatIsReallyStored
——————
0x00009620016CE3A8
(1 row(s) affected)
I must confess, I am no bit head who thinks in hexadecimal, so I do have my problems telling at a glance that it is now 2005-03-23 22:08:31.280. 😉
SQL Server Books Online (BOL) (in its January 2004 version) states that “Values with the DATETIME data type are stored internally by Microsoft SQL Server as two 4-byte integers.”
Nothing more and nothing less!
Note, it does not say that the data is stored in a specific date and/or time format, which might depend on some language or other computer settings! Just two integer numbers are stored. And while that’s correct, it is not 100% correct. Sure, there are two 4-byte integers stored. But they are packed together into a BINARY(8). The first 4-byte being the elapsed number days since SQL Server’s base date of 19000101.
The Second 4-bytes Store the Time of Day Represented as the Number of Milliseconds After Midnight
Again, this you can read in the January 2004 edition of the BOL. Let’s test it out with a simple script:
DECLARE @300 BINARY(8) SET @300 = 0x00000000 + CAST(300 AS BINARY(4))
Before discussing the result, what will this script do? First, we declare a variable in SQL Server’s internal DATETIME format. We then set the first 4-bytes (that is the date part) = 0x00000000. That way we set it to the Server’s base date of 0 (=19000101). We then concatenate the date part with a time part. I’ve chosen intentionally the value of 300, because, according to BOL, it should represent 300 milliseconds, so about 1/3 of a second. So, now, we’re ready to let the script run:
SELECT @300 , CAST(@300 AS DATETIME)
—————— ——————————————————
0x000000000000012C 1900-01-01 00:00:01.000
(1 row(s) affected)
Oops, we get 1 second, not the expected 300 milliseconds. So, doing some reverse conclusion, when the integer value of 300 is causing the time to show 1 second, then it cannot represent 300 milliseconds, but rather 300/300 of one second. And that is 1 second.
Actually, SQL Server does store there the clock-ticks since midnight. Each clock-tick is equivalent to 3.33 milliseconds. That’s also the reason why the DATETIME datatype has an accuracy of one three-hundredth of a second. This, again, is correctly stated in BOL.