{"id":145,"date":"2018-06-30T18:00:14","date_gmt":"2018-06-30T18:00:14","guid":{"rendered":"https:\/\/datablog.roman-halliday.com\/?p=145"},"modified":"2018-06-17T12:59:50","modified_gmt":"2018-06-17T12:59:50","slug":"dates-in-sql-server-ambiguous-date-specification","status":"publish","type":"post","link":"https:\/\/datablog.roman-halliday.com\/index.php\/2018\/06\/30\/dates-in-sql-server-ambiguous-date-specification\/","title":{"rendered":"Dates In SQL Server: Ambiguous date specification"},"content":{"rendered":"<p>When writing a date in a script, there are a number of ways of doing it, the server uses geographic settings to imply the format. This has some hidden dangers.<\/p>\n<p>UK and US dates switch the day and month around. If there are changes in geographic settings and the date format isn&#8217;t explicit, there can be unexpected consequences. The best outcome we can hope for is that the date given isn&#8217;t valid e.g. The 12th day of the 30th month (when we wanted 30th December). The worst is when we modify the numbers for the 12th October, rather than the 10th December!<\/p>\n<p>This is something to put on any migration\/upgrade roadmap. Especially when migrating\/consolidating projects to new environments, or combining processes in a multinational organisation.<\/p>\n<h1>The solutions<\/h1>\n<p>When we write code using dates, let&#8217;s not be ambiguous with our formatting. If we use methods which remove the need for the DBMS to guess, it can&#8217;t get it wrong.<\/p>\n<ul>\n<li>For SQL Server versions after 2012, use <a href=\"https:\/\/docs.microsoft.com\/en-us\/sql\/t-sql\/functions\/datefromparts-transact-sql?view=sql-server-2017\">DateFromParts(YYYY, MM, DD)<\/a><\/li>\n<li>For SQL Server versions 2005 onwards, use <code>'DD-MON-YY'<\/code><\/li>\n<li>If you must use another format like ISO, Specify the format: <code>CONVERT(DATE, 'YYYY-MM-DD', 120)<\/code><\/li>\n<\/ul>\n<p>DateFromParts translates easily over geographic settings without ambiguity and is easy to read.<\/p>\n<p>The <code>DD-MON-YY<\/code> format isn&#8217;t as ambiguous as others (England and America two nations divided by a common language and how to write dates). I tend to use it for smaller tasks and ad-hoc scripts as it&#8217;s easy to read.<\/p>\n<h1>The danger demonstrated<\/h1>\n<p>The below code samples highlight this, you can see them in action on\u00a0<a href=\"http:\/\/rextester.com\/TVTUT82156\">SQL Server &#8211; Date Danger<\/a><\/p>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"sql\">--------------------------------------------------------------------------------\r\n-- View the setting as it is right now\r\n--------------------------------------------------------------------------------\r\nSELECT [dateformat] FROM syslanguages WHERE [name] = @@LANGUAGE;\r\n\r\n\r\n--------------------------------------------------------------------------------\r\n-- Change to US and test\r\n--------------------------------------------------------------------------------\r\nSET DATEFORMAT DMY;\r\n\r\nSELECT [dateformat] FROM syslanguages WHERE [name] = @@LANGUAGE;\r\n\r\n-- Convert to date without specifying format\r\n\r\nSELECT CONVERT(DATETIME, '2014-12-10') AS date_test;\r\n\r\n-- This will cause an error\r\nSELECT CONVERT(DATETIME, '2014-12-15') AS date_test;\r\n\r\n-- Using a numeric argument to CONVERT to specify the date format\r\n\r\nSELECT CONVERT(DATETIME, '2014-12-10', 120) AS date_test;\r\n\r\nSELECT CONVERT(DATETIME, '2014-12-15', 120) AS date_test;\r\n\r\n--------------------------------------------------------------------------------\r\n-- Change to UK and test\r\n--------------------------------------------------------------------------------\r\nSET DATEFORMAT MDY;\r\n\r\nSELECT [dateformat] FROM syslanguages WHERE [name] = @@LANGUAGE;\r\n\r\n-- Convert to date without specifying format\r\n\r\nSELECT CONVERT(DATETIME, '2014-12-10') AS date_test;\r\n\r\nSELECT CONVERT(DATETIME, '2014-12-15') AS date_test;\r\n\r\n--Using a numeric argument to CONVERT to specify the date format\r\n\r\nSELECT CONVERT(DATETIME, '2014-12-10', 120) AS date_test;\r\n\r\nSELECT CONVERT(DATETIME, '2014-12-15', 120) AS date_test;<\/pre>\n<p>&nbsp;<\/p>\n","protected":false},"excerpt":{"rendered":"<p>When writing a date in a script, there are a number of ways of doing it, the server uses geographic settings to imply the format. This has some hidden dangers. UK and US dates switch the day and month around. If there are changes in geographic settings and the date format isn&#8217;t explicit, there can&hellip;<\/p>\n<p class=\"read-more\"><a class=\"readmore-btn\" href=\"https:\/\/datablog.roman-halliday.com\/index.php\/2018\/06\/30\/dates-in-sql-server-ambiguous-date-specification\/\">Read More<span class=\"screen-reader-text\">  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-145","post","type-post","status-publish","format-standard","hentry","category-databases","category-sql-server","tag-date"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v27.5 - https:\/\/yoast.com\/product\/yoast-seo-wordpress\/ -->\n<title>Dates In SQL Server: Ambiguous date specification - Rows Across The Lake<\/title>\n<meta name=\"description\" content=\"When writing a date in a script, there are a number of ways of doing it, the server uses geographic settings to imply the format. This has some hidden dangers.\" \/>\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\/06\/30\/dates-in-sql-server-ambiguous-date-specification\/\" \/>\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: Ambiguous date specification - Rows Across The Lake\" \/>\n<meta property=\"og:description\" content=\"When writing a date in a script, there are a number of ways of doing it, the server uses geographic settings to imply the format. This has some hidden dangers.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/datablog.roman-halliday.com\/index.php\/2018\/06\/30\/dates-in-sql-server-ambiguous-date-specification\/\" \/>\n<meta property=\"og:site_name\" content=\"Rows Across The Lake\" \/>\n<meta property=\"article:published_time\" content=\"2018-06-30T18:00:14+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\\\/06\\\/30\\\/dates-in-sql-server-ambiguous-date-specification\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/datablog.roman-halliday.com\\\/index.php\\\/2018\\\/06\\\/30\\\/dates-in-sql-server-ambiguous-date-specification\\\/\"},\"author\":{\"name\":\"david\",\"@id\":\"https:\\\/\\\/datablog.roman-halliday.com\\\/#\\\/schema\\\/person\\\/575f96d2590c3085923ff9e1b565748b\"},\"headline\":\"Dates In SQL Server: Ambiguous date specification\",\"datePublished\":\"2018-06-30T18:00:14+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/datablog.roman-halliday.com\\\/index.php\\\/2018\\\/06\\\/30\\\/dates-in-sql-server-ambiguous-date-specification\\\/\"},\"wordCount\":274,\"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\\\/06\\\/30\\\/dates-in-sql-server-ambiguous-date-specification\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/datablog.roman-halliday.com\\\/index.php\\\/2018\\\/06\\\/30\\\/dates-in-sql-server-ambiguous-date-specification\\\/\",\"url\":\"https:\\\/\\\/datablog.roman-halliday.com\\\/index.php\\\/2018\\\/06\\\/30\\\/dates-in-sql-server-ambiguous-date-specification\\\/\",\"name\":\"Dates In SQL Server: Ambiguous date specification - Rows Across The Lake\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/datablog.roman-halliday.com\\\/#website\"},\"datePublished\":\"2018-06-30T18:00:14+00:00\",\"description\":\"When writing a date in a script, there are a number of ways of doing it, the server uses geographic settings to imply the format. This has some hidden dangers.\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/datablog.roman-halliday.com\\\/index.php\\\/2018\\\/06\\\/30\\\/dates-in-sql-server-ambiguous-date-specification\\\/#breadcrumb\"},\"inLanguage\":\"en-GB\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/datablog.roman-halliday.com\\\/index.php\\\/2018\\\/06\\\/30\\\/dates-in-sql-server-ambiguous-date-specification\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/datablog.roman-halliday.com\\\/index.php\\\/2018\\\/06\\\/30\\\/dates-in-sql-server-ambiguous-date-specification\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/datablog.roman-halliday.com\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Dates In SQL Server: Ambiguous date specification\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/datablog.roman-halliday.com\\\/#website\",\"url\":\"https:\\\/\\\/datablog.roman-halliday.com\\\/\",\"name\":\"Rows Across The Lake\",\"description\":\"Data &amp; 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: Ambiguous date specification - Rows Across The Lake","description":"When writing a date in a script, there are a number of ways of doing it, the server uses geographic settings to imply the format. This has some hidden dangers.","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\/06\/30\/dates-in-sql-server-ambiguous-date-specification\/","og_locale":"en_GB","og_type":"article","og_title":"Dates In SQL Server: Ambiguous date specification - Rows Across The Lake","og_description":"When writing a date in a script, there are a number of ways of doing it, the server uses geographic settings to imply the format. This has some hidden dangers.","og_url":"https:\/\/datablog.roman-halliday.com\/index.php\/2018\/06\/30\/dates-in-sql-server-ambiguous-date-specification\/","og_site_name":"Rows Across The Lake","article_published_time":"2018-06-30T18:00:14+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\/06\/30\/dates-in-sql-server-ambiguous-date-specification\/#article","isPartOf":{"@id":"https:\/\/datablog.roman-halliday.com\/index.php\/2018\/06\/30\/dates-in-sql-server-ambiguous-date-specification\/"},"author":{"name":"david","@id":"https:\/\/datablog.roman-halliday.com\/#\/schema\/person\/575f96d2590c3085923ff9e1b565748b"},"headline":"Dates In SQL Server: Ambiguous date specification","datePublished":"2018-06-30T18:00:14+00:00","mainEntityOfPage":{"@id":"https:\/\/datablog.roman-halliday.com\/index.php\/2018\/06\/30\/dates-in-sql-server-ambiguous-date-specification\/"},"wordCount":274,"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\/06\/30\/dates-in-sql-server-ambiguous-date-specification\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/datablog.roman-halliday.com\/index.php\/2018\/06\/30\/dates-in-sql-server-ambiguous-date-specification\/","url":"https:\/\/datablog.roman-halliday.com\/index.php\/2018\/06\/30\/dates-in-sql-server-ambiguous-date-specification\/","name":"Dates In SQL Server: Ambiguous date specification - Rows Across The Lake","isPartOf":{"@id":"https:\/\/datablog.roman-halliday.com\/#website"},"datePublished":"2018-06-30T18:00:14+00:00","description":"When writing a date in a script, there are a number of ways of doing it, the server uses geographic settings to imply the format. This has some hidden dangers.","breadcrumb":{"@id":"https:\/\/datablog.roman-halliday.com\/index.php\/2018\/06\/30\/dates-in-sql-server-ambiguous-date-specification\/#breadcrumb"},"inLanguage":"en-GB","potentialAction":[{"@type":"ReadAction","target":["https:\/\/datablog.roman-halliday.com\/index.php\/2018\/06\/30\/dates-in-sql-server-ambiguous-date-specification\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/datablog.roman-halliday.com\/index.php\/2018\/06\/30\/dates-in-sql-server-ambiguous-date-specification\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/datablog.roman-halliday.com\/"},{"@type":"ListItem","position":2,"name":"Dates In SQL Server: Ambiguous date specification"}]},{"@type":"WebSite","@id":"https:\/\/datablog.roman-halliday.com\/#website","url":"https:\/\/datablog.roman-halliday.com\/","name":"Rows Across The Lake","description":"Data &amp; 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\/145","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=145"}],"version-history":[{"count":2,"href":"https:\/\/datablog.roman-halliday.com\/index.php\/wp-json\/wp\/v2\/posts\/145\/revisions"}],"predecessor-version":[{"id":150,"href":"https:\/\/datablog.roman-halliday.com\/index.php\/wp-json\/wp\/v2\/posts\/145\/revisions\/150"}],"wp:attachment":[{"href":"https:\/\/datablog.roman-halliday.com\/index.php\/wp-json\/wp\/v2\/media?parent=145"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/datablog.roman-halliday.com\/index.php\/wp-json\/wp\/v2\/categories?post=145"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/datablog.roman-halliday.com\/index.php\/wp-json\/wp\/v2\/tags?post=145"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}