{"id":6754,"date":"2013-12-07T00:00:53","date_gmt":"2013-12-07T00:00:53","guid":{"rendered":"http:\/\/craftydba.com\/?p=6754"},"modified":"2016-04-20T02:01:47","modified_gmt":"2016-04-20T02:01:47","slug":"datetime-functions-isdate","status":"publish","type":"post","link":"https:\/\/craftydba.com\/?p=6754","title":{"rendered":"Date\/Time Functions \u2013 ISDATE()"},"content":{"rendered":"<p>I am tireless with my writing of short articles or tidbits on Transaction SQL <a href=\"http:\/\/technet.microsoft.com\/en-us\/library\/ms186724.aspx\">date\/time functions<\/a>.<\/p>\n<p>Most, if not all, modern day computer systems collect data which contains dates and times.<\/p>\n<p>Sometimes data comes from external sources in a textual format.  How do we know if the data is a valid date\/time variable?<\/p>\n<p>Today, I will be exploring the <a href=\"http:\/\/technet.microsoft.com\/en-us\/library\/ms187347.aspx\">ISDATE()<\/a> function that takes a character string as input and returns an integer as output.  A numeric 1 represents a valid date and a numeric 0 represents an invalid date.<\/p>\n<p>The examples below are sample calls to the function. The inputs puts are valid strings, invalid strings, a null value and a empty string.<\/p>\n<pre class=\"lang:TSQL theme:familiar mark:1,2-3\" title=\"isdate() function\">\r\n\/*\r\n    Is my string a valid date or time?\r\n*\/\r\n\r\n-- Valid date\/time strings\r\nSELECT \r\n    'VALID DATE TIME' AS HEADER,\r\n    ISDATE('2013-12-10 12:32:10.250') AS FLAG\r\n    UNION ALL\r\nSELECT\r\n    'VALID DATE' AS HEADER,\r\n    ISDATE('2013-12-10') AS FLAG\r\n    UNION ALL\r\nSELECT\r\n    'VALID TIME' AS HEADER,\r\n    ISDATE('12:32:10.250') AS FLAG\r\n    UNION ALL\r\n\r\n-- Missing or empty strings\r\nSELECT\r\n    'NULL VALUE' AS HEADER,\r\n    ISDATE(NULL) AS FLAG\r\n    UNION ALL\r\nSELECT\r\n    'EMPTY STRING' AS HEADER,\r\n    ISDATE('') AS FLAG\r\n    UNION ALL\r\n\r\n-- Invalid date\/time strings\r\nSELECT \r\n    'INVALID DATE TIME' AS HEADER,\r\n    ISDATE('2013-42-10 12:70:10.250') AS FLAG\r\n    UNION ALL\r\nSELECT\r\n    'INVALID DATE' AS HEADER,\r\n    ISDATE('2013-12-52') AS FLAG\r\n    UNION ALL\r\nSELECT\r\n    'INVALID TIME' AS HEADER,\r\n    ISDATE('42:32:10.250') AS FLAG;\r\nGO\r\n\r\n<\/pre>\n<\/p>\n<p>The output from the test is listed below. <\/p>\n<pre class=\"lang:TSQL theme:epicgeeks\" title=\"output\">\r\noutput: \r\n\r\nHEADER            FLAG\r\n----------------- -----------\r\nVALID DATE TIME   1\r\nVALID DATE        1\r\nVALID TIME        1\r\nNULL VALUE        0\r\nEMPTY STRING      0\r\nINVALID DATE TIME 0\r\nINVALID DATE      0\r\nINVALID TIME      0\r\n<\/pre>\n<\/p>\n<p>In summary, the <a href=\"http:\/\/technet.microsoft.com\/en-us\/library\/ms187347.aspx\">ISDATE()<\/a> function is a quick way to determine if a string can be converted to a date\/time variable.  Next time, I will be talking about the end of month function EOMONTH() which was introduced to TSQL in 2012.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>I am tireless with my writing of short articles or tidbits on Transaction SQL date\/time functions. Most, if not all, modern day computer systems collect data which contains dates and times. Sometimes data comes from external sources in a textual format. How do we know if the data is a valid date\/time variable? Today, I will be exploring the ISDATE() function that takes a character string as input and returns an integer as output. A numeric 1 represents a valid date and a numeric 0 represents an invalid date. The&hellip;<\/p>\n","protected":false},"author":1,"featured_media":6759,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[814],"tags":[31,940,994,993,15,29],"class_list":["post-6754","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-very-short-articles","tag-database-developer","tag-date-time-functions","tag-is-a-valid-date","tag-isdate","tag-john-f-miner-iii","tag-tsql"],"_links":{"self":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts\/6754","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=6754"}],"version-history":[{"count":0,"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts\/6754\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/media\/6759"}],"wp:attachment":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=6754"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=6754"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=6754"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}