I am a eager beaver 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 add or subtract a fixed number of years, months, or etc from a date time variable.
Today, I will be exploring the DATEADD() function. This function has 13 different input parameters that can be passed to change the resulting output.
To summarize, the function takes a date part, a offset number, and date/time variables as input returns a new date/time variable adjusted by adding or subtracting the offset.
I will be exploring the three date part parameters that adjust date/time variable by a fixed time offset.
| 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 numeric offsets 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 27 |
-- -- DATEADD() - Function calls to Add/Sub Fixed Amount from D/T. -- -- Hours DECLARE @DT1 SMALLDATETIME = '2013-12-10 12:32:10.12'; SELECT DATEADD(HH, -1, @DT1) AS HOUR_PREV, DATEADD(HH, 0, @DT1) AS HOUR_CURR, DATEADD(HH, 1, @DT1) AS HOUR_NEXT GO -- Minutes DECLARE @DT1 SMALLDATETIME = '2013-12-10 12:32:10.12'; SELECT DATEADD(MI, -1, @DT1) AS MINUTE_PREV, DATEADD(MI, 0, @DT1) AS MINUTE_CURR, DATEADD(N, 1, @DT1) AS MINUTE_NEXT GO -- Seconds DECLARE @DT1 DATETIME = '2013-12-10 12:32:10.12'; SELECT DATEADD(SS, -1, @DT1) AS SECOND_PREV, DATEADD(SS, 0, @DT1) AS SECOND_CURR, DATEADD(S, 1, @DT1) AS SECOND_NEXT GO |
The output from the test is listed below.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
output: HOUR_PREV HOUR_CURR HOUR_NEXT ----------------------- ----------------------- ----------------------- 2013-12-10 11:32:00 2013-12-10 12:32:00 2013-12-10 13:32:00 MINUTE_PREV MINUTE_CURR MINUTE_NEXT ----------------------- ----------------------- ----------------------- 2013-12-10 12:31:00 2013-12-10 12:32:00 2013-12-10 12:33:00 SECOND_PREV SECOND_CURR SECOND_NEXT ----------------------- ----------------------- ----------------------- 2013-12-10 12:32:09.120 2013-12-10 12:32:10.120 2013-12-10 12:32:11.120 |
To make a long story short, pass one of the three date part parameters with a valid offset number to adjust your current date time variable. Next time, I will be talking about how to use this function with Sub-Second Related Parts.
