{"id":103,"date":"2015-02-11T00:00:00","date_gmt":"2013-01-01T00:00:00","guid":{"rendered":"https:\/\/www.sqlhammer.com\/blog\/derik-hammer-presents-fun-with-datetime"},"modified":"2026-03-18T21:51:36","modified_gmt":"2026-03-18T21:51:36","slug":"derik-hammer-presents-fun-with-datetime","status":"publish","type":"post","link":"https:\/\/sqlhammer.com\/index.php\/2015\/02\/11\/derik-hammer-presents-fun-with-datetime\/","title":{"rendered":"Derik Hammer Presents, Fun with DATETIME"},"content":{"rendered":"

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, \u201cDATETIMEs are fun.\u201d Depending upon who you are talking to, they might be\u00a0FUN<\/em>, or\u00a0\u201cfun\u201d. <\/em>The issue that we experienced was rooted in using the wrong data-type, but I\u2019m getting ahead of myself. Let us demonstrate a couple of differences between DATETIME and DATETIME2.<\/p>\n

The documentation<\/h2>\n

The DATETIME<\/a> data-type has been replaced with the newer DATETIME2<\/a> date-type. To be clear, I\u2019m not saying that DATETIME is going away. In fact, I doubt that it will disappear at all. Microsoft does state, however\u2026<\/p>\n

> Use the time, date, datetime2 and datetimeoffset data types for new work.<\/strong> These types align with the SQL Standard<\/a>. They are more portable. time, datetime2 and datetimeoffset provide more seconds precision. datetimeoffsetprovides time zone support for globally deployed applications.<\/p>\n

In addition to Microsoft\u2019s recommendation, and the additional precision, DATETIME2 provides better storage space usage. It will use, \u201c6 bytes for precisions less than 3; 7 bytes for precisions 3 and 4. All other precisions require 8 bytes.\u201d 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.<\/p>\n

You can read up on a few more reasons to begin using DATETIME2 here<\/a>, as well.<\/p>\n

Numeric conversions<\/h2>\n

The problem that my co-worker ran into was regarding an unintentional conversion between an INT and DATETIME.\u00a0You 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<\/a> which claims that the FILETIME<\/a>.aspx) structure is being used to store the date and time. \u00a0Some trial and error, however, shows the Min and Max dates to be different.\u00a0Quick demo time.<\/p>\n

\"\"<\/a><\/p>\n

\"\"<\/a><\/p>\n

You can see here that January, 1st 1753\u00a0is 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 \u201czero date\u201d and then worked the numbers backwards and forwards in single day increments. 1753, as most of us know, is the minimum date\/time value\u00a0(or was, we\u2019ll talk more about that with DATETIME2) and this is supposedly because of a calendar recalculation<\/a> which leaves 1752 missing a number of days.<\/p>\n

\"\"<\/a><\/p>\n

As I already mentioned, DATETIME2 does not allow for numeric conversions.<\/p>\n

\"\"<\/p>\n

In addition, the MIN(DATETIME2) appears to be of the year 0001, not 1753. This means that developers can start using the DateTime.Min<\/a>.aspx) property again, rather than SqlDateTime.Min<\/a>.aspx).<\/p>\n

Years and sliding windows<\/h2>\n

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.<\/p>\n

\"\"<\/a><\/p>\n

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.<\/p>\n

First, we will stage some data.<\/p>\n

\"\"<\/a><\/p>\n

\"\"<\/a><\/p>\n

Now we can build our queries.<\/p>\n

\"\"<\/a><\/p>\n

\"\"<\/a><\/p>\n

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\u2019t have any data in 1905.<\/p>\n

\"\"<\/p>\n

\"\"<\/a><\/p>\n

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.<\/p>\n

\n

\"\"Written by Derik Hammer of SQL Hammer<\/a><\/strong><\/p>\n

Derik is a data professional focusing on Microsoft SQL Server. His passion focuses around high-availability<\/a>, disaster recovery<\/a>, continuous integration, and automated maintenance. his experience has spanned long-term database administration, consulting, and\u00a0entrepreneurial ventures.<\/p>\n

Derik gives the SQL community credit for plugging the gaps in his knowledge when he was a junior DBA and, now that his skills have matured, started SQLHammer.com<\/a> as one small way to give back and continue the cycle of shared learning.<\/p>\n

Derik is the owner and lead author of SQL Hammer, a Microsoft SQL Server resource<\/a>.<\/p>\n

For more information, visit http:\/\/www.sqlhammer.com<\/a>. Follow Derik on Twitter<\/a> for SQL tips and chat<\/strong><\/p>\n","protected":false},"excerpt":{"rendered":"

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, \u201cDATETIMEs are fun.\u201d Depending upon who you are talking to, they might […]<\/p>\n","protected":false},"author":1,"featured_media":573,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-103","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-uncategorized"],"_links":{"self":[{"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/posts\/103","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/comments?post=103"}],"version-history":[{"count":1,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/posts\/103\/revisions"}],"predecessor-version":[{"id":365,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/posts\/103\/revisions\/365"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/media\/573"}],"wp:attachment":[{"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/media?parent=103"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/categories?post=103"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/tags?post=103"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}