I am steady 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 seven date part parameters that adjust date/time variable by a fixed date offset.
| 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 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 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 |
-- -- DATEADD() - Function calls to Add/Sub Fixed Amount from D/T. -- -- Years DECLARE @DT1 SMALLDATETIME = '2013-12-10 12:32:10.12'; SELECT DATEADD(YYYY, -1, @DT1) AS YEAR_PREV, DATEADD(YY, 0, @DT1) AS YEAR_CURR, DATEADD(YY, 1, @DT1) AS YEAR_NEXT GO -- Quarters DECLARE @DT1 SMALLDATETIME = '2013-12-10 12:32:10.12'; SELECT DATEADD(QQ, -1, @DT1) AS QUARTER_PREV, DATEADD(QQ, 0, @DT1) AS QUARTER_CURR, DATEADD(Q, 1, @DT1) AS QUARTER_NEXT GO -- Months DECLARE @DT1 SMALLDATETIME = '2013-12-10 12:32:10.12'; SELECT DATEADD(MM, -1, @DT1) AS MONTH_PREV, DATEADD(M, 0, @DT1) AS MONTH_CURR, DATEADD(M, 1, @DT1) AS MONTH_NEXT GO -- Day of year DECLARE @DT1 SMALLDATETIME = '2013-12-10 12:32:10.12'; SELECT DATEADD(DY, -5, @DT1) AS DOY_5_PREV, DATEADD(DY, 0, @DT1) AS DOY_CURR, DATEADD(Y, 5, @DT1) AS DOY_5_NEXT GO -- Days DECLARE @DT1 SMALLDATETIME = '2013-12-10 12:32:10.12'; SELECT DATEADD(DD, -1, @DT1) AS DAY_PREV, DATEADD(D, 0, @DT1) AS DAY_CURR, DATEADD(D, 1, @DT1) AS DAY_NEXT GO -- Weeks DECLARE @DT1 SMALLDATETIME = '2013-12-10 12:32:10.12'; SELECT DATEADD(WK, -2, @DT1) AS WEEKS_2_PREV, DATEADD(WK, 0, @DT1) AS WEEKS_CURR, DATEADD(WW, 2, @DT1) AS WEEKS_2_NEXT GO |
The output from the test is listed below.
|
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 |
output: YEAR_PREV YEAR_CURR YEAR_NEXT ----------------------- ----------------------- ----------------------- 2012-12-10 12:32:00 2013-12-10 12:32:00 2014-12-10 12:32:00 QUARTER_PREV QUARTER_CURR QUARTER_NEXT ----------------------- ----------------------- ----------------------- 2013-09-10 12:32:00 2013-12-10 12:32:00 2014-03-10 12:32:00 MONTH_PREV MONTH_CURR MONTH_NEXT ----------------------- ----------------------- ----------------------- 2013-11-10 12:32:00 2013-12-10 12:32:00 2014-01-10 12:32:00 DOY_5_PREV DOY_CURR DOY_5_NEXT ----------------------- ----------------------- ----------------------- 2013-12-05 12:32:00 2013-12-10 12:32:00 2013-12-15 12:32:00 DAY_PREV DAY_CURR DAY_NEXT ----------------------- ----------------------- ----------------------- 2013-12-09 12:32:00 2013-12-10 12:32:00 2013-12-11 12:32:00 WEEKS_2_PREV WEEKS_CURR WEEKS_2_NEXT ----------------------- ----------------------- ----------------------- 2013-11-26 12:32:00 2013-12-10 12:32:00 2013-12-24 12:32:00 |
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. I am only going to do this for one date part since they all act the same.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
-- -- DATEADD() - Negative test case for year only. -- -- Null Test #1 DECLARE @DT1 DATETIME2 = '2013-12-10 12:32:10.12'; SELECT DATEADD(YY, NULL, @DT1) AS ADD_YEARS_NULL_P2 GO -- Null Test #2 SELECT DATEADD(YY, -1, NULL) AS ADD_YEARS_NULL_P3 GO -- Empty String Test #1 DECLARE @DT1 DATETIME2 = '2013-12-10 12:32:10.12'; SELECT DATEADD(YY, '', @DT1) AS ADD_YEARS_NULL_P2 GO -- Empty String Test #2 SELECT DATEADD(YY, -1, '') AS ADD_YEARS_NULL_P3 GO |
The output from the test is listed below. Any invalid data to the numeric offset results in a raised error. Any NULL values passed as a date time result in NULL and EMPTY strings result in a default date.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
output: Msg 8116, Level 16, State 1, Line 3 Argument data type NULL is invalid for argument 2 of dateadd function. ADD_YEARS_NULL_P3 ----------------------- NULL (1 row(s) affected) Msg 8116, Level 16, State 1, Line 4 Argument data type varchar is invalid for argument 2 of dateadd function. ADD_YEARS_NULL_P3 ----------------------- 1899-01-01 00:00:00.000 |
To make a long story short, pass one of the seven 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 Time Related Parts.
