I am constant 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 calculate the difference between two given dates using a particular unit of measure (date part) like hours, minutes, or seconds.
Today, I will be exploring the DATEDIFF() function. This function has 12 different input parameters that can be passed to change the resulting output.
To summarize, the function takes a date part, a start date/time variable and a end date/time variable as input returns the calculated difference in the correct unit of measure.
I will be exploring the three time part parameters that determine the units of measure
DATE PART | PARAMETER TO USE |
HOUR | HH |
MINUTE | MI or N |
SECOND | SS or S |
The examples below are sample calls to the function. The inputs puts are valid date part parameters and date time variables.
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 |
-- -- Find the difference in time parts (unit of measure) -- -- Hours DECLARE @DT1 DATETIME2 = '2013-12-10 12:32:10.12'; DECLARE @DT2 DATETIME2 = '2013-12-12 12:32:10.12'; SELECT DATEDIFF(HH, @DT1, @DT2) AS DIFF_HH1 GO -- Minutes DECLARE @DT1 DATETIME2 = '2013-12-10 12:32:10.12'; DECLARE @DT2 DATETIME2 = '2013-12-12 12:32:10.12'; SELECT DATEDIFF(MI, @DT1, @DT2) AS DIFF_MI1, DATEDIFF(N, @DT1, @DT2) AS DIFF_MI2 GO -- Seconds DECLARE @DT1 DATETIME2 = '2013-12-10 12:32:10.12'; DECLARE @DT2 DATETIME2 = '2013-12-12 12:32:10.12'; SELECT DATEDIFF(SS, @DT1, @DT2) AS DIFF_SS1, DATEDIFF(S, @DT1, @DT2) AS DIFF_SS2 GO |
The output from the test is listed below.
1 2 3 4 5 6 7 8 9 10 11 12 13 |
output: DIFF_HH1 ----------- 48 DIFF_MI1 DIFF_MI2 ----------- ----------- 2880 2880 DIFF_SS1 DIFF_SS2 ----------- ----------- 172800 172800 |
Like any good unit test, one should try both positive and negative test cases. I am not going to explore how an EMPTY string or NULL value effect the results provided by the function.
However, the following rules apply. Any invalid date parameter results in a raised error. Any NULL values passed as parameters result in a NULL result. Any EMPTY strings result in a default date.
To make a long story short, pass one of the three date part parameters (time) with two date/time variables to calculated the difference. Next time, I will be talking about how to use this function with Sub-Second related Parts.