{"id":6677,"date":"2013-11-30T00:00:58","date_gmt":"2013-11-30T00:00:58","guid":{"rendered":"http:\/\/craftydba.com\/?p=6677"},"modified":"2016-04-20T14:15:58","modified_gmt":"2016-04-20T14:15:58","slug":"datename-sub-second-functions","status":"publish","type":"post","link":"https:\/\/craftydba.com\/?p=6677","title":{"rendered":"DATENAME() – Sub-second Functions"},"content":{"rendered":"

I am unwavering in my creation of short articles or tidbits on Transaction SQL date\/time functions<\/a>. <\/p>\n

Most, if not all, modern day computer systems collect data which contains dates and times. Many times you will want to convert the data from one data type to another. <\/p>\n

Today, I will be exploring the DATENAME()<\/a> function. This function has 15 different input parameters that can be passed to change the resulting output. <\/p>\n

In short, the function takes a date part and date\/time variables as input returns the part that you want a character string data. <\/p>\n

I will be exploring the three date part parameters that convert the variable to a sub-second time string.<\/p>\n\n\n\n\n\n\n\n
DATE PART<\/td>\nPARAMETER TO USE<\/td>\n<\/tr>\n<\/thead>\n
MILLISCOND<\/td>\nMS<\/td>\n<\/tr>\n
MICROSECOND<\/td>\nMCS<\/td>\n<\/tr>\n
NANASECOND<\/td>\nNS<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

 \n<\/p>\n

The examples below are sample calls to the function. The inputs puts are valid date, null value and a empty string. <\/p>\n

\r\n--\r\n--  Functions to get date and time parts as TEXT\r\n-- \r\n\r\n-- Millisecond ~ varchar(3) ~ 000 to 999\r\nSELECT \r\n  SYSDATETIME() AS FULL_DATE1,\r\n  DATENAME(MS, SYSDATETIME()) AS MILLISEC_TXT3,\r\n  DATENAME(MS, NULL) AS MILLISEC_NULL,\r\n  DATENAME(MS, '') AS MILLISEC_EMPTY\r\nGO\r\n\r\n\r\n-- Microsecond ~ varchar(6) ~ 000000 to 999999\r\nSELECT \r\n  SYSDATETIME() AS FULL_DATE2,\r\n  DATENAME(MCS, SYSDATETIME()) AS MICROSEC_TXT6,\r\n  DATENAME(MCS, NULL) AS MICROSEC_NULL,\r\n  DATENAME(MCS, '') AS MICROSEC_EMPTY\r\nGO\r\n\r\n-- Nanosecond ~ varchar(9) ~ 0000000 00 to 9999999 00\r\nSELECT \r\n  SYSDATETIME() AS FULL_DATE3,\r\n  DATENAME(NS, SYSDATETIME()) AS NANOSEC_TXT9,\r\n  DATENAME(NS, NULL) AS NANOSEC_NULL,\r\n  DATENAME(NS, '') AS NANOSEC_EMPTY\r\nGO\r\n\r\n<\/pre>\n<\/p>\n
The output from the test is listed below.  It is surprising that an empty string returns a selected default value.<\/p>\n
\r\noutput: \r\n\r\nFULL_DATE1                  MILLISEC_TXT3                  MILLISEC_NULL                  MILLISEC_EMPTY\r\n--------------------------- ------------------------------ ------------------------------ ------------------------------\r\n2013-12-06 14:57:52.0292439 29                             NULL                           0\r\n\r\n\r\nFULL_DATE2                  MICROSEC_TXT6                  MICROSEC_NULL                  MICROSEC_EMPTY\r\n--------------------------- ------------------------------ ------------------------------ ------------------------------\r\n2013-12-06 14:57:52.0632439 63243                          NULL                           0\r\n\r\n\r\nFULL_DATE3                  NANOSEC_TXT9                   NANOSEC_NULL                   NANOSEC_EMPTY\r\n--------------------------- ------------------------------ ------------------------------ ------------------------------\r\n2013-12-06 14:57:52.0692439 69243900                       NULL                           0\r\n<\/pre>\n<\/p>\n
A brief summary of this article, pass one of the three date part parameters to extract the sub-seconds time in a string format.<\/p>\n","protected":false},"excerpt":{"rendered":"
I am unwavering in my creation 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. Many times you will want to convert the data from one data type to another. Today, I will be exploring the DATENAME() function. This function has 15 different input parameters that can be passed to change the resulting output. In short, the function takes a date part and date\/time variables as input returns the part that you want a…<\/p>\n","protected":false},"author":1,"featured_media":6617,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[814],"tags":[31,940,609,15,989,29],"class_list":["post-6677","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-very-short-articles","tag-database-developer","tag-date-time-functions","tag-datename","tag-john-f-miner-iii","tag-sub-second-related-parts","tag-tsql"],"_links":{"self":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts\/6677","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=6677"}],"version-history":[{"count":0,"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts\/6677\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/media\/6617"}],"wp:attachment":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=6677"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=6677"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=6677"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}