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 character string data.
I will be exploring the seven date part parameters that convert the date variable to a string.
| DATE PART | PARAMETER TO USE |
| YEAR | YYYY or YY |
| QUARTER | QQ or Q |
| MONTH | MM or M |
| DAY OF YEAR | DY or Y |
| DAY | DD or D |
| WEEK | WK or WW |
| DAY OF WEEK | DW or W |
The examples below are sample calls to the function. The inputs puts are valid date, null value and a empty string.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 |
-- -- Functions to get date and time parts as TEXT -- -- Year ~ varchar(4) ~ 2013 > SELECT DATENAME(YYYY, SYSDATETIME()) AS YEAR_A_TXT4, DATENAME(YY, SYSDATETIME()) AS YEAR_B_TXT4, DATENAME(YYYY, NULL) AS YEAR_C_NULL, DATENAME(YY, '') AS YEAR_D_EMPTY GO -- Quarter ~ varchar(1) ~ 1 to 4 SELECT DATENAME(QQ, SYSDATETIME()) AS QUARTER_A_TXT1, DATENAME(Q, SYSDATETIME()) AS QUARTER_B_TXT1, DATENAME(QQ, NULL) AS QUARTER_C_NULL, DATENAME(Q, '') AS QUARTER_D_EMPTY GO -- Month ~ varchar(9) ~ January to December SELECT DATENAME(MM, SYSDATETIME()) AS MONTH_A_TXT9, DATENAME(M, SYSDATETIME()) AS MONTH_B_TXT9, DATENAME(MM, NULL) AS MONTH_C_NULL, DATENAME(M, '') AS MONTH_D_EMPTY GO -- Day of year ~ varchar(3) ~ 1 to 366 SELECT DATENAME(DY, SYSDATETIME()) AS DAYOFYR_A_TXT3, DATENAME(Y, SYSDATETIME()) AS DAYOFYR_B_TXT3, DATENAME(DY, NULL) AS DAYOFYR_C_NULL, DATENAME(Y, '') AS DAYOFYR_D_EMPTY GO -- Day ~ varchar(2) ~ 1 to 31 SELECT DATENAME(DD, SYSDATETIME()) AS DAY_A_TXT2, DATENAME(D, SYSDATETIME()) AS DAY_B_TXT2, DATENAME(DD, NULL) AS DAY_C_NULL, DATENAME(D, '') AS DAY_D_EMPTY GO -- Week ~ varchar(2) ~ 1 to 52 SELECT DATENAME(WK, SYSDATETIME()) AS WEEK_A_TXT2, DATENAME(WW, SYSDATETIME()) AS WEEK_B_TXT2, DATENAME(WK, NULL) AS WEEK_C_NULL, DATENAME(WW, '') AS WEEK_D_EMPTY GO -- Week day ~ varchar(9) ~ Sunday to Saturday SELECT DATENAME(DW, SYSDATETIME()) AS WEEKDAY_A_TXT9, DATENAME(W, SYSDATETIME()) AS WEEKDAY_B_TXT9, DATENAME(DW, NULL) AS WEEKDAY_C_NULL, DATENAME(W, '') AS WEEKDAY_D_EMPTY GO |
The output from the test is listed below. It is surprising that an empty string returns a selected default value.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 |
output: YEAR_A_TXT4 YEAR_B_TXT4 YEAR_C_NULL YEAR_D_EMPTY ------------------------------ ------------------------------ ------------------------------ ------------------------------ 2013 2013 NULL 1900 QUARTER_A_TXT1 QUARTER_B_TXT1 QUARTER_C_NULL QUARTER_D_EMPTY ------------------------------ ------------------------------ ------------------------------ ------------------------------ 4 4 NULL 1 MONTH_A_TXT9 MONTH_B_TXT9 MONTH_C_NULL MONTH_D_EMPTY ------------------------------ ------------------------------ ------------------------------ ------------------------------ December December NULL January DAYOFYR_A_TXT3 DAYOFYR_B_TXT3 DAYOFYR_C_NULL DAYOFYR_D_EMPTY ------------------------------ ------------------------------ ------------------------------ ------------------------------ 340 340 NULL 1 DAY_A_TXT2 DAY_B_TXT2 DAY_C_NULL DAY_D_EMPTY ------------------------------ ------------------------------ ------------------------------ ------------------------------ 6 6 NULL 1 WEEK_A_TXT2 WEEK_B_TXT2 WEEK_C_NULL WEEK_D_EMPTY ------------------------------ ------------------------------ ------------------------------ ------------------------------ 49 49 NULL 1 WEEKDAY_A_TXT9 WEEKDAY_B_TXT9 WEEKDAY_C_NULL WEEKDAY_D_EMPTY ------------------------------ ------------------------------ ------------------------------ ------------------------------ Friday Friday NULL Monday |
To make a long story short, pass one of the seven date part parameters to extract the data in a string format.
