{"id":73,"date":"2018-05-15T18:00:24","date_gmt":"2018-05-15T18:00:24","guid":{"rendered":"https:\/\/datablog.roman-halliday.com\/?p=73"},"modified":"2018-06-08T09:55:22","modified_gmt":"2018-06-08T09:55:22","slug":"dates-in-sql-server-datetime2-vs-smalldatetime","status":"publish","type":"post","link":"https:\/\/datablog.roman-halliday.com\/index.php\/2018\/05\/15\/dates-in-sql-server-datetime2-vs-smalldatetime\/","title":{"rendered":"Dates In SQL Server: DATETIME2 vs SMALLDATETIME"},"content":{"rendered":"

While working on the first article about dates Dates In SQL Server: Managing & Manipulating Dates<\/a>, I touched on the datatypes and realised that it’s worth devoting some time to the subtle but potentially dangerous difference between these two.<\/p>\n

I previously said:<\/p>\n

If you need to include time (so not just the whole day) then use DATETIME2<\/code> or SMALLDATETIME<\/code> (unless you need the backwards compatibility of DATETIME<\/code>).<\/p><\/blockquote>\n

Most people look at SMALLDATETIME<\/code> and see that it uses less storage (this can be very handy when looking at very large tables). In most cases now, disk space is cheap and the advantage of the space saving is negligible.<\/p>\n

The key difference between the two that is often overlooked (dangerously), is that they round differently. DATETIME2<\/code> always rounds down (depending on it’s level of precision). SMALLDATETIME<\/code> rounds to the closest. With SMALLDATETIME<\/code> activity can get attributed to the next day\/time period<\/em> which may not be the desired result.\u00a0The last record for Tuesday could become stored and managed as the first record for Wednesday! This can have a massive impact on a report or business process.<\/p>\n

I have taken one of the samples mentioned in stack overflow here<\/a>\u00a0and built it in SQL Fiddle. the way the day can change with the SMALLDATETIME<\/code> rounding.<\/p>\n

Examples<\/h2>\n
--------------------------------------------------------------------------------\r\n-- Create Table\r\n--------------------------------------------------------------------------------\r\nCREATE TABLE t\r\n(\r\n  sdt SMALLDATETIME,\r\n  dt2 DATETIME2(2)\r\n);\r\n\r\n--------------------------------------------------------------------------------\r\n-- Populate\r\n--------------------------------------------------------------------------------\r\nWITH date_time_values AS (\r\nSELECT '2017-01-01T11:22:22.33' AS [Date_Value]\r\nUNION ALL\r\nSELECT '2017-01-01T11:22:33.33' AS [Date_Value]\r\nUNION ALL\r\nSELECT '2017-01-01T23:59:59.59' AS [Date_Value]\r\n)\r\nINSERT\r\n  INTO t\r\nSELECT Date_Value, Date_Value\r\n  FROM date_time_values \r\n;\r\n\r\n--------------------------------------------------------------------------------\r\n-- View Conversions\r\n--------------------------------------------------------------------------------\r\nSELECT sdt               AS [ISO Date Time],\r\n       dt2               AS DATETIME2,\r\n       CAST(sdt AS DATE) AS SMALLDATETIME_DATE,\r\n       CAST(dt2 AS DATE) AS DATETIME2_DATE\r\n  FROM t\r\n;<\/pre>\n\n\n\n\n\t\n\n\t\n\t\n\t
ISO Date Time<\/th>DATETIME2<\/th>SMALLDATETIME_DATE<\/th>DATETIME2_DATE<\/th>\n<\/tr>\n<\/thead>\n
2017-01-01T11:22:00Z<\/td>2017-01-01 11:22:22.33<\/td>2017-01-01<\/td>2017-01-01<\/td>\n<\/tr>\n
2017-01-01T11:23:00Z<\/td>2017-01-01 11:22:33.33<\/td>2017-01-01<\/td>2017-01-01<\/td>\n<\/tr>\n
2017-01-02T00:00:00Z<\/td>2017-01-01 23:59:59.59<\/td>2017-01-02<\/td>2017-01-01<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n\n
In the above, the last row shows how the end of one day can become the start of the next.<\/p>\n
To see it in action, have a look at the\u00a0SQL Fiddle – DateTime2 VS SMALLDATE TIME<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"
While working on the first article about dates Dates In SQL Server: Managing & Manipulating Dates, I touched on the datatypes and realised that it’s worth devoting some time to the subtle but potentially dangerous difference between these two. I previously said: If you need to include time (so not just the whole day) then…<\/p>\n
Read More  Read More<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3,4],"tags":[12],"class_list":["post-73","post","type-post","status-publish","format-standard","hentry","category-databases","category-sql-server","tag-date"],"yoast_head":"\nDates In SQL Server: DATETIME2 vs SMALLDATETIME - Rows Across The Lake<\/title>\n<meta name=\"description\" content=\"While working on the first article about dates Dates In SQL Server: Managing & Manipulating Dates, I touched on the datatypes and realised that it's worth devoting some time to the subtle but potentially dangerous difference between these two.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/datablog.roman-halliday.com\/index.php\/2018\/05\/15\/dates-in-sql-server-datetime2-vs-smalldatetime\/\" \/>\n<meta property=\"og:locale\" content=\"en_GB\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Dates In SQL Server: DATETIME2 vs SMALLDATETIME - Rows Across The Lake\" \/>\n<meta property=\"og:description\" content=\"While working on the first article about dates Dates In SQL Server: Managing & Manipulating Dates, I touched on the datatypes and realised that it's worth devoting some time to the subtle but potentially dangerous difference between these two.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/datablog.roman-halliday.com\/index.php\/2018\/05\/15\/dates-in-sql-server-datetime2-vs-smalldatetime\/\" \/>\n<meta property=\"og:site_name\" content=\"Rows Across The Lake\" \/>\n<meta property=\"article:published_time\" content=\"2018-05-15T18:00:24+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2018-06-08T09:55:22+00:00\" \/>\n<meta name=\"author\" content=\"david\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@d_roman_h\" \/>\n<meta name=\"twitter:site\" content=\"@d_roman_h\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"david\" \/>\n\t<meta name=\"twitter:label2\" content=\"Estimated reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/datablog.roman-halliday.com\\\/index.php\\\/2018\\\/05\\\/15\\\/dates-in-sql-server-datetime2-vs-smalldatetime\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/datablog.roman-halliday.com\\\/index.php\\\/2018\\\/05\\\/15\\\/dates-in-sql-server-datetime2-vs-smalldatetime\\\/\"},\"author\":{\"name\":\"david\",\"@id\":\"https:\\\/\\\/datablog.roman-halliday.com\\\/#\\\/schema\\\/person\\\/575f96d2590c3085923ff9e1b565748b\"},\"headline\":\"Dates In SQL Server: DATETIME2 vs SMALLDATETIME\",\"datePublished\":\"2018-05-15T18:00:24+00:00\",\"dateModified\":\"2018-06-08T09:55:22+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/datablog.roman-halliday.com\\\/index.php\\\/2018\\\/05\\\/15\\\/dates-in-sql-server-datetime2-vs-smalldatetime\\\/\"},\"wordCount\":254,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\\\/\\\/datablog.roman-halliday.com\\\/#\\\/schema\\\/person\\\/575f96d2590c3085923ff9e1b565748b\"},\"keywords\":[\"date\"],\"articleSection\":[\"Databases\",\"SQL Server\"],\"inLanguage\":\"en-GB\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/datablog.roman-halliday.com\\\/index.php\\\/2018\\\/05\\\/15\\\/dates-in-sql-server-datetime2-vs-smalldatetime\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/datablog.roman-halliday.com\\\/index.php\\\/2018\\\/05\\\/15\\\/dates-in-sql-server-datetime2-vs-smalldatetime\\\/\",\"url\":\"https:\\\/\\\/datablog.roman-halliday.com\\\/index.php\\\/2018\\\/05\\\/15\\\/dates-in-sql-server-datetime2-vs-smalldatetime\\\/\",\"name\":\"Dates In SQL Server: DATETIME2 vs SMALLDATETIME - Rows Across The Lake\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/datablog.roman-halliday.com\\\/#website\"},\"datePublished\":\"2018-05-15T18:00:24+00:00\",\"dateModified\":\"2018-06-08T09:55:22+00:00\",\"description\":\"While working on the first article about dates Dates In SQL Server: Managing & Manipulating Dates, I touched on the datatypes and realised that it's worth devoting some time to the subtle but potentially dangerous difference between these two.\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/datablog.roman-halliday.com\\\/index.php\\\/2018\\\/05\\\/15\\\/dates-in-sql-server-datetime2-vs-smalldatetime\\\/#breadcrumb\"},\"inLanguage\":\"en-GB\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/datablog.roman-halliday.com\\\/index.php\\\/2018\\\/05\\\/15\\\/dates-in-sql-server-datetime2-vs-smalldatetime\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/datablog.roman-halliday.com\\\/index.php\\\/2018\\\/05\\\/15\\\/dates-in-sql-server-datetime2-vs-smalldatetime\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/datablog.roman-halliday.com\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Dates In SQL Server: DATETIME2 vs SMALLDATETIME\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/datablog.roman-halliday.com\\\/#website\",\"url\":\"https:\\\/\\\/datablog.roman-halliday.com\\\/\",\"name\":\"Rows Across The Lake\",\"description\":\"Data & Databases\",\"publisher\":{\"@id\":\"https:\\\/\\\/datablog.roman-halliday.com\\\/#\\\/schema\\\/person\\\/575f96d2590c3085923ff9e1b565748b\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/datablog.roman-halliday.com\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-GB\"},{\"@type\":[\"Person\",\"Organization\"],\"@id\":\"https:\\\/\\\/datablog.roman-halliday.com\\\/#\\\/schema\\\/person\\\/575f96d2590c3085923ff9e1b565748b\",\"name\":\"david\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-GB\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/acddbc676a1d5c73795edcf0627ee39e5aa947da9033b58373e03d93122cb3b7?s=96&d=mm&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/acddbc676a1d5c73795edcf0627ee39e5aa947da9033b58373e03d93122cb3b7?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/acddbc676a1d5c73795edcf0627ee39e5aa947da9033b58373e03d93122cb3b7?s=96&d=mm&r=g\",\"caption\":\"david\"},\"logo\":{\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/acddbc676a1d5c73795edcf0627ee39e5aa947da9033b58373e03d93122cb3b7?s=96&d=mm&r=g\"}}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Dates In SQL Server: DATETIME2 vs SMALLDATETIME - Rows Across The Lake","description":"While working on the first article about dates Dates In SQL Server: Managing & Manipulating Dates, I touched on the datatypes and realised that it's worth devoting some time to the subtle but potentially dangerous difference between these two.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/datablog.roman-halliday.com\/index.php\/2018\/05\/15\/dates-in-sql-server-datetime2-vs-smalldatetime\/","og_locale":"en_GB","og_type":"article","og_title":"Dates In SQL Server: DATETIME2 vs SMALLDATETIME - Rows Across The Lake","og_description":"While working on the first article about dates Dates In SQL Server: Managing & Manipulating Dates, I touched on the datatypes and realised that it's worth devoting some time to the subtle but potentially dangerous difference between these two.","og_url":"https:\/\/datablog.roman-halliday.com\/index.php\/2018\/05\/15\/dates-in-sql-server-datetime2-vs-smalldatetime\/","og_site_name":"Rows Across The Lake","article_published_time":"2018-05-15T18:00:24+00:00","article_modified_time":"2018-06-08T09:55:22+00:00","author":"david","twitter_card":"summary_large_image","twitter_creator":"@d_roman_h","twitter_site":"@d_roman_h","twitter_misc":{"Written by":"david","Estimated reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/datablog.roman-halliday.com\/index.php\/2018\/05\/15\/dates-in-sql-server-datetime2-vs-smalldatetime\/#article","isPartOf":{"@id":"https:\/\/datablog.roman-halliday.com\/index.php\/2018\/05\/15\/dates-in-sql-server-datetime2-vs-smalldatetime\/"},"author":{"name":"david","@id":"https:\/\/datablog.roman-halliday.com\/#\/schema\/person\/575f96d2590c3085923ff9e1b565748b"},"headline":"Dates In SQL Server: DATETIME2 vs SMALLDATETIME","datePublished":"2018-05-15T18:00:24+00:00","dateModified":"2018-06-08T09:55:22+00:00","mainEntityOfPage":{"@id":"https:\/\/datablog.roman-halliday.com\/index.php\/2018\/05\/15\/dates-in-sql-server-datetime2-vs-smalldatetime\/"},"wordCount":254,"commentCount":0,"publisher":{"@id":"https:\/\/datablog.roman-halliday.com\/#\/schema\/person\/575f96d2590c3085923ff9e1b565748b"},"keywords":["date"],"articleSection":["Databases","SQL Server"],"inLanguage":"en-GB","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/datablog.roman-halliday.com\/index.php\/2018\/05\/15\/dates-in-sql-server-datetime2-vs-smalldatetime\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/datablog.roman-halliday.com\/index.php\/2018\/05\/15\/dates-in-sql-server-datetime2-vs-smalldatetime\/","url":"https:\/\/datablog.roman-halliday.com\/index.php\/2018\/05\/15\/dates-in-sql-server-datetime2-vs-smalldatetime\/","name":"Dates In SQL Server: DATETIME2 vs SMALLDATETIME - Rows Across The Lake","isPartOf":{"@id":"https:\/\/datablog.roman-halliday.com\/#website"},"datePublished":"2018-05-15T18:00:24+00:00","dateModified":"2018-06-08T09:55:22+00:00","description":"While working on the first article about dates Dates In SQL Server: Managing & Manipulating Dates, I touched on the datatypes and realised that it's worth devoting some time to the subtle but potentially dangerous difference between these two.","breadcrumb":{"@id":"https:\/\/datablog.roman-halliday.com\/index.php\/2018\/05\/15\/dates-in-sql-server-datetime2-vs-smalldatetime\/#breadcrumb"},"inLanguage":"en-GB","potentialAction":[{"@type":"ReadAction","target":["https:\/\/datablog.roman-halliday.com\/index.php\/2018\/05\/15\/dates-in-sql-server-datetime2-vs-smalldatetime\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/datablog.roman-halliday.com\/index.php\/2018\/05\/15\/dates-in-sql-server-datetime2-vs-smalldatetime\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/datablog.roman-halliday.com\/"},{"@type":"ListItem","position":2,"name":"Dates In SQL Server: DATETIME2 vs SMALLDATETIME"}]},{"@type":"WebSite","@id":"https:\/\/datablog.roman-halliday.com\/#website","url":"https:\/\/datablog.roman-halliday.com\/","name":"Rows Across The Lake","description":"Data & Databases","publisher":{"@id":"https:\/\/datablog.roman-halliday.com\/#\/schema\/person\/575f96d2590c3085923ff9e1b565748b"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/datablog.roman-halliday.com\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-GB"},{"@type":["Person","Organization"],"@id":"https:\/\/datablog.roman-halliday.com\/#\/schema\/person\/575f96d2590c3085923ff9e1b565748b","name":"david","image":{"@type":"ImageObject","inLanguage":"en-GB","@id":"https:\/\/secure.gravatar.com\/avatar\/acddbc676a1d5c73795edcf0627ee39e5aa947da9033b58373e03d93122cb3b7?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/acddbc676a1d5c73795edcf0627ee39e5aa947da9033b58373e03d93122cb3b7?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/acddbc676a1d5c73795edcf0627ee39e5aa947da9033b58373e03d93122cb3b7?s=96&d=mm&r=g","caption":"david"},"logo":{"@id":"https:\/\/secure.gravatar.com\/avatar\/acddbc676a1d5c73795edcf0627ee39e5aa947da9033b58373e03d93122cb3b7?s=96&d=mm&r=g"}}]}},"_links":{"self":[{"href":"https:\/\/datablog.roman-halliday.com\/index.php\/wp-json\/wp\/v2\/posts\/73","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/datablog.roman-halliday.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/datablog.roman-halliday.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/datablog.roman-halliday.com\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/datablog.roman-halliday.com\/index.php\/wp-json\/wp\/v2\/comments?post=73"}],"version-history":[{"count":7,"href":"https:\/\/datablog.roman-halliday.com\/index.php\/wp-json\/wp\/v2\/posts\/73\/revisions"}],"predecessor-version":[{"id":117,"href":"https:\/\/datablog.roman-halliday.com\/index.php\/wp-json\/wp\/v2\/posts\/73\/revisions\/117"}],"wp:attachment":[{"href":"https:\/\/datablog.roman-halliday.com\/index.php\/wp-json\/wp\/v2\/media?parent=73"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/datablog.roman-halliday.com\/index.php\/wp-json\/wp\/v2\/categories?post=73"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/datablog.roman-halliday.com\/index.php\/wp-json\/wp\/v2\/tags?post=73"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}