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 character string data.
I will be exploring the three date part parameters that convert the variable to a sub-second time string.
DATE PART | PARAMETER TO USE |
MILLISCOND | MS |
MICROSECOND | MCS |
NANASECOND | NS |
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 |
-- -- Functions to get date and time parts as TEXT -- -- Millisecond ~ varchar(3) ~ 000 to 999 SELECT SYSDATETIME() AS FULL_DATE1, DATENAME(MS, SYSDATETIME()) AS MILLISEC_TXT3, DATENAME(MS, NULL) AS MILLISEC_NULL, DATENAME(MS, '') AS MILLISEC_EMPTY GO -- Microsecond ~ varchar(6) ~ 000000 to 999999 SELECT SYSDATETIME() AS FULL_DATE2, DATENAME(MCS, SYSDATETIME()) AS MICROSEC_TXT6, DATENAME(MCS, NULL) AS MICROSEC_NULL, DATENAME(MCS, '') AS MICROSEC_EMPTY GO -- Nanosecond ~ varchar(9) ~ 0000000 00 to 9999999 00 SELECT SYSDATETIME() AS FULL_DATE3, DATENAME(NS, SYSDATETIME()) AS NANOSEC_TXT9, DATENAME(NS, NULL) AS NANOSEC_NULL, DATENAME(NS, '') AS NANOSEC_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 |
output: FULL_DATE1 MILLISEC_TXT3 MILLISEC_NULL MILLISEC_EMPTY --------------------------- ------------------------------ ------------------------------ ------------------------------ 2013-12-06 14:57:52.0292439 29 NULL 0 FULL_DATE2 MICROSEC_TXT6 MICROSEC_NULL MICROSEC_EMPTY --------------------------- ------------------------------ ------------------------------ ------------------------------ 2013-12-06 14:57:52.0632439 63243 NULL 0 FULL_DATE3 NANOSEC_TXT9 NANOSEC_NULL NANOSEC_EMPTY --------------------------- ------------------------------ ------------------------------ ------------------------------ 2013-12-06 14:57:52.0692439 69243900 NULL 0 |
A brief summary of this article, pass one of the three date part parameters to extract the sub-seconds time in a string format.