As my regular readers might have noticed, I get a lot of my ideas for this blog from my daily life. This post is no different. I was helping a co-worker troubleshoot an incorrect result-set. After we had corrected the problem he stated, “DATETIMEs are fun.” Depending upon who you are talking to, they might be FUN, or “fun”. The issue that we experienced was rooted in using the wrong data-type, but I’m getting ahead of myself. Let us demonstrate a couple of differences between DATETIME and DATETIME2.
The DATETIME data-type has been replaced with the newer DATETIME2 date-type. To be clear, I’m not saying that DATETIME is going away. In fact, I doubt that it will disappear at all. Microsoft does state, however…
Use the time, date, datetime2 and datetimeoffset data types for new work. These types align with the SQL Standard. They are more portable. time, datetime2 and datetimeoffset provide more seconds precision. datetimeoffsetprovides time zone support for globally deployed applications.
In addition to Microsoft’s recommendation, and the additional precision, DATETIME2 provides better storage space usage. It will use, “6 bytes for precisions less than 3; 7 bytes for precisions 3 and 4. All other precisions require 8 bytes.” DATETIME is a solid 8 bytes with a precision of 3. Therefore, DATETIME2 is 1 byte less for an equivalent precision and 2 bytes less if you reduce the precision.
You can read up on a few more reasons to begin using DATETIME2 here, as well.
The problem that my co-worker ran into was regarding an unintentional conversion between an INT and DATETIME. You can convert an INT to the DATETIME data-type but you cannot convert between INT and the DATETIME2 data-type. A little bit of research had me stumble upon this post which claims that the FILETIME structure is being used to store the date and time. Some trial and error, however, shows the Min and Max dates to be different. Quick demo time.
You can see here that January, 1st 1753 is the MIN(DATETIME) value represented by -53690. The MAX(DATETIME) is December 31st, 9999, represented by 2958463. It appears that Microsoft selected 1900 as their “zero date” and then worked the numbers backwards and forwards in single day increments. 1753, as most of us know, is the minimum date/time value (or was, we’ll talk more about that with DATETIME2) and this is supposedly because of a calendar recalculation which leaves 1752 missing a number of days.
As I already mentioned, DATETIME2 does not allow for numeric conversions.
Years and sliding windows
The report that was being troubleshot was attempting to create a sliding window. data was to be analyzed based on a start and end date input. The year was to be disregarded and key off of a table but data should only be returned for records which fell in the month and day ranges for each year in the table. This is where the numeric conversion because an issue.
The year column in the table was a SMALLINT which made a CAST to DATETIME result in July 8th, 1905 instead of 2014. As seen above, using a character string for the year gives us the correct results. This problem is moot for DATETIME2 because a numeric conversion is disallowed. Here is the complete scenario to drive the point home.
First, we will stage some data.
Now we can build our queries.
In this query, the YEAR() function returns an INT which satisfies the JOIN. However, the direct conversion to DATETIME ends up not returning any rows because we don’t have any data in 1905.
This, corrected, query converts first to a CHAR(4) and then to DATETIME to work around the numeric conversion. The end result is a successful sliding window displaying results for parts of January, February, and March.