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 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 as an integer. I will be concentrating on how to extract time as sub-seconds in this article.
We can extract milliseconds (MS), microseconds (MCS) and nanoseconds (NS) from the date time variable given the correct date part parameter.
The examples below are sample calls to the function. The inputs puts are all valid dates.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
-- -- Functions to get date and time parts as INT -- -- Millisecond SELECT SYSDATETIME() AS FULL_DATE1, DATEPART(MS, SYSDATETIME()) AS MILLISEC_INT GO -- Microsecond SELECT SYSDATETIME() AS FULL_DATE2, DATEPART(MCS, SYSDATETIME()) AS MICROSEC_INT GO -- Nanosecond SELECT SYSDATETIME() AS FULL_DATE3, DATEPART(NS, SYSDATETIME()) AS NANOSEC_INT GO |
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.
The output from the above test is listed below.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
output: FULL_DATE1 MILLISEC1_INT --------------------------- ------------- 2013-12-01 17:38:25.9886582 988 FULL_DATE2 MICROSEC_INT --------------------------- ------------ 2013-12-01 17:38:26.0006588 658 FULL_DATE3 NANOSEC_INT --------------------------- ----------- 2013-12-01 17:38:26.0056591 5659100 |
In as nutshell, pass the correct date part parameters to extract the chosen sub-second, as an integer, from the date time variable.