{"id":6581,"date":"2013-11-20T00:00:47","date_gmt":"2013-11-20T00:00:47","guid":{"rendered":"http:\/\/craftydba.com\/?p=6581"},"modified":"2016-04-20T17:17:07","modified_gmt":"2016-04-20T17:17:07","slug":"datetime-datepart-function-sub-second","status":"publish","type":"post","link":"https:\/\/craftydba.com\/?p=6581","title":{"rendered":"DATEPART() Function \u2013 Subseconds"},"content":{"rendered":"<p>I am firm in my decision to write another 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>Not only is it important to know when a sales order came in, but we should fulfill the request using some type of queue (<a href=\"http:\/\/en.wikipedia.org\/wiki\/LIFO_(computing)\">LIFO<\/a>).  If if was a stack (<a href=\"http:\/\/en.wikipedia.org\/wiki\/FIFO\">FIFO<\/a>) implementation, the first person may never get his order!<\/p>\n<p>Today, I will be exploring the <a href=\"http:\/\/technet.microsoft.com\/en-us\/library\/ms174420.aspx\">DATEPART()<\/a> function.  This function has 15 different input parameters that can be passed to change the resulting output.  <\/p>\n<p>In short, the function takes a date part and date time variables as input returns the part that you want as an integer.  I will be concentrating on how to extract time as sub-seconds in this article.  <\/p>\n<p>We can extract milliseconds (MS), microseconds (MCS) and nanoseconds (NS) from the date time variable given the correct date part parameter.<\/p>\n<p>The examples below are sample calls to the function.  The inputs puts are all valid dates.  <\/p>\n<pre class=\"lang:TSQL theme:familiar mark:1,2-3\" title=\"datepart() function - sub seconds\">\r\n--\r\n--  Functions to get date and time parts as INT\r\n-- \r\n\r\n-- Millisecond\r\nSELECT \r\n  SYSDATETIME() AS FULL_DATE1,\r\n  DATEPART(MS, SYSDATETIME()) AS MILLISEC_INT\r\nGO\r\n\r\n-- Microsecond\r\nSELECT \r\n  SYSDATETIME() AS FULL_DATE2,\r\n  DATEPART(MCS, SYSDATETIME()) AS MICROSEC_INT\r\nGO\r\n\r\n-- Nanosecond\r\nSELECT \r\n  SYSDATETIME() AS FULL_DATE3,\r\n  DATEPART(NS, SYSDATETIME()) AS NANOSEC_INT\r\nGO\r\n\r\n<\/pre>\n<\/p>\n<p>You can validate these results on your own.  A null value as input will return a null value as output.  A empty string as an input will return a 0 as an output.<\/p>\n<p>The output from the above test is listed below.  <\/p>\n<pre class=\"lang:TSQL theme:epicgeeks\" title=\"output\">\r\noutput: \r\n\r\nFULL_DATE1                  MILLISEC1_INT\r\n--------------------------- -------------\r\n2013-12-01 17:38:25.9886582 988\r\n\r\n\r\nFULL_DATE2                  MICROSEC_INT\r\n--------------------------- ------------\r\n2013-12-01 17:38:26.0006588 658\r\n\r\n\r\nFULL_DATE3                  NANOSEC_INT\r\n--------------------------- -----------\r\n2013-12-01 17:38:26.0056591 5659100\r\n\r\n<\/pre>\n<\/p>\n<p>In as nutshell, pass the correct date part parameters to extract the chosen sub-second, as an integer, from the date time variable.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>I am firm in my decision to write another 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. Not only is it important to know when a sales order came in, but we should fulfill the request using some type of queue (LIFO). If if was a stack (FIFO) implementation, the first person may never get his order! Today, I will be exploring the DATEPART() function. This function has 15 different input parameters that can&hellip;<\/p>\n","protected":false},"author":1,"featured_media":6486,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[814],"tags":[31,940,608,15,977,974,976,975,978,973],"class_list":["post-6581","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-very-short-articles","tag-database-developer","tag-date-time-functions","tag-datepart","tag-john-f-miner-iii","tag-mcs","tag-milliseconds","tag-ms","tag-nanosecond","tag-ns","tag-tsql-microsecond"],"_links":{"self":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts\/6581","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=6581"}],"version-history":[{"count":0,"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts\/6581\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/media\/6486"}],"wp:attachment":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=6581"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=6581"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=6581"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}