\n| DAY OF WEEK<\/td>\n | DW or W<\/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--\r\n-- Functions to get date and time parts as TEXT\r\n-- \r\n\r\n-- Year ~ varchar(4) ~ 2013 >\r\nSELECT \r\n DATENAME(YYYY, SYSDATETIME()) AS YEAR_A_TXT4,\r\n DATENAME(YY, SYSDATETIME()) AS YEAR_B_TXT4,\r\n DATENAME(YYYY, NULL) AS YEAR_C_NULL,\r\n DATENAME(YY, '') AS YEAR_D_EMPTY\r\nGO\r\n\r\n\r\n-- Quarter ~ varchar(1) ~ 1 to 4\r\nSELECT \r\n DATENAME(QQ, SYSDATETIME()) AS QUARTER_A_TXT1,\r\n DATENAME(Q, SYSDATETIME()) AS QUARTER_B_TXT1,\r\n DATENAME(QQ, NULL) AS QUARTER_C_NULL,\r\n DATENAME(Q, '') AS QUARTER_D_EMPTY\r\nGO\r\n\r\n\r\n-- Month ~ varchar(9) ~ January to December\r\nSELECT \r\n DATENAME(MM, SYSDATETIME()) AS MONTH_A_TXT9,\r\n DATENAME(M, SYSDATETIME()) AS MONTH_B_TXT9,\r\n DATENAME(MM, NULL) AS MONTH_C_NULL,\r\n DATENAME(M, '') AS MONTH_D_EMPTY\r\nGO\r\n\r\n\r\n-- Day of year ~ varchar(3) ~ 1 to 366\r\nSELECT \r\n DATENAME(DY, SYSDATETIME()) AS DAYOFYR_A_TXT3,\r\n DATENAME(Y, SYSDATETIME()) AS DAYOFYR_B_TXT3,\r\n DATENAME(DY, NULL) AS DAYOFYR_C_NULL,\r\n DATENAME(Y, '') AS DAYOFYR_D_EMPTY\r\nGO\r\n\r\n\r\n-- Day ~ varchar(2) ~ 1 to 31\r\nSELECT \r\n DATENAME(DD, SYSDATETIME()) AS DAY_A_TXT2,\r\n DATENAME(D, SYSDATETIME()) AS DAY_B_TXT2,\r\n DATENAME(DD, NULL) AS DAY_C_NULL,\r\n DATENAME(D, '') AS DAY_D_EMPTY\r\nGO\r\n\r\n\r\n-- Week ~ varchar(2) ~ 1 to 52\r\nSELECT \r\n DATENAME(WK, SYSDATETIME()) AS WEEK_A_TXT2,\r\n DATENAME(WW, SYSDATETIME()) AS WEEK_B_TXT2,\r\n DATENAME(WK, NULL) AS WEEK_C_NULL,\r\n DATENAME(WW, '') AS WEEK_D_EMPTY\r\nGO\r\n\r\n-- Week day ~ varchar(9) ~ Sunday to Saturday\r\nSELECT \r\n DATENAME(DW, SYSDATETIME()) AS WEEKDAY_A_TXT9,\r\n DATENAME(W, SYSDATETIME()) AS WEEKDAY_B_TXT9,\r\n DATENAME(DW, NULL) AS WEEKDAY_C_NULL,\r\n DATENAME(W, '') AS WEEKDAY_D_EMPTY\r\nGO\r\n<\/pre>\n<\/p>\nThe 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\nYEAR_A_TXT4 YEAR_B_TXT4 YEAR_C_NULL YEAR_D_EMPTY\r\n------------------------------ ------------------------------ ------------------------------ ------------------------------\r\n2013 2013 NULL 1900\r\n\r\n\r\nQUARTER_A_TXT1 QUARTER_B_TXT1 QUARTER_C_NULL QUARTER_D_EMPTY\r\n------------------------------ ------------------------------ ------------------------------ ------------------------------\r\n4 4 NULL 1\r\n\r\n\r\nMONTH_A_TXT9 MONTH_B_TXT9 MONTH_C_NULL MONTH_D_EMPTY\r\n------------------------------ ------------------------------ ------------------------------ ------------------------------\r\nDecember December NULL January\r\n\r\n\r\nDAYOFYR_A_TXT3 DAYOFYR_B_TXT3 DAYOFYR_C_NULL DAYOFYR_D_EMPTY\r\n------------------------------ ------------------------------ ------------------------------ ------------------------------\r\n340 340 NULL 1\r\n\r\n\r\nDAY_A_TXT2 DAY_B_TXT2 DAY_C_NULL DAY_D_EMPTY\r\n------------------------------ ------------------------------ ------------------------------ ------------------------------\r\n6 6 NULL 1\r\n\r\n\r\nWEEK_A_TXT2 WEEK_B_TXT2 WEEK_C_NULL WEEK_D_EMPTY\r\n------------------------------ ------------------------------ ------------------------------ ------------------------------\r\n49 49 NULL 1\r\n\r\n\r\nWEEKDAY_A_TXT9 WEEKDAY_B_TXT9 WEEKDAY_C_NULL WEEKDAY_D_EMPTY\r\n------------------------------ ------------------------------ ------------------------------ ------------------------------\r\nFriday Friday NULL Monday\r\n<\/pre>\n<\/p>\nTo make a long story short, pass one of the seven date part parameters to extract the data in a string format.<\/p>\n","protected":false},"excerpt":{"rendered":" I am untiring 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,987,940,609,15,29],"class_list":["post-6658","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-very-short-articles","tag-database-developer","tag-date-related-parts","tag-date-time-functions","tag-datename","tag-john-f-miner-iii","tag-tsql"],"_links":{"self":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts\/6658","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=6658"}],"version-history":[{"count":0,"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts\/6658\/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=6658"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=6658"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=6658"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}} |