\n| DAY OF WEEK<\/td>\n | DW or W<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n \n<\/p>\n The examples below are sample calls to the function. The inputs puts are valid numeric offsets and date time variables. <\/p>\n \r\n--\r\n-- DATEADD() - Function calls to Add\/Sub Fixed Amount from D\/T.\r\n--\r\n\r\n-- Years\r\nDECLARE @DT1 SMALLDATETIME = '2013-12-10 12:32:10.12';\r\nSELECT \r\n DATEADD(YYYY, -1, @DT1) AS YEAR_PREV,\r\n DATEADD(YY, 0, @DT1) AS YEAR_CURR,\r\n DATEADD(YY, 1, @DT1) AS YEAR_NEXT\r\nGO\r\n\r\n-- Quarters\r\nDECLARE @DT1 SMALLDATETIME = '2013-12-10 12:32:10.12';\r\nSELECT \r\n DATEADD(QQ, -1, @DT1) AS QUARTER_PREV,\r\n DATEADD(QQ, 0, @DT1) AS QUARTER_CURR,\r\n DATEADD(Q, 1, @DT1) AS QUARTER_NEXT\r\nGO\r\n\r\n-- Months\r\nDECLARE @DT1 SMALLDATETIME = '2013-12-10 12:32:10.12';\r\nSELECT \r\n DATEADD(MM, -1, @DT1) AS MONTH_PREV,\r\n DATEADD(M, 0, @DT1) AS MONTH_CURR,\r\n DATEADD(M, 1, @DT1) AS MONTH_NEXT\r\nGO\r\n\r\n-- Day of year\r\nDECLARE @DT1 SMALLDATETIME = '2013-12-10 12:32:10.12';\r\nSELECT \r\n DATEADD(DY, -5, @DT1) AS DOY_5_PREV,\r\n DATEADD(DY, 0, @DT1) AS DOY_CURR,\r\n DATEADD(Y, 5, @DT1) AS DOY_5_NEXT\r\nGO\r\n\r\n-- Days\r\nDECLARE @DT1 SMALLDATETIME = '2013-12-10 12:32:10.12';\r\nSELECT \r\n DATEADD(DD, -1, @DT1) AS DAY_PREV,\r\n DATEADD(D, 0, @DT1) AS DAY_CURR,\r\n DATEADD(D, 1, @DT1) AS DAY_NEXT\r\nGO\r\n\r\n-- Weeks\r\nDECLARE @DT1 SMALLDATETIME = '2013-12-10 12:32:10.12';\r\nSELECT \r\n DATEADD(WK, -2, @DT1) AS WEEKS_2_PREV,\r\n DATEADD(WK, 0, @DT1) AS WEEKS_CURR,\r\n DATEADD(WW, 2, @DT1) AS WEEKS_2_NEXT\r\nGO\r\n\r\n<\/pre>\n<\/p>\nThe output from the test is listed below. <\/p>\n \r\noutput: \r\n\r\nYEAR_PREV YEAR_CURR YEAR_NEXT\r\n----------------------- ----------------------- -----------------------\r\n2012-12-10 12:32:00 2013-12-10 12:32:00 2014-12-10 12:32:00\r\n\r\nQUARTER_PREV QUARTER_CURR QUARTER_NEXT\r\n----------------------- ----------------------- -----------------------\r\n2013-09-10 12:32:00 2013-12-10 12:32:00 2014-03-10 12:32:00\r\n\r\nMONTH_PREV MONTH_CURR MONTH_NEXT\r\n----------------------- ----------------------- -----------------------\r\n2013-11-10 12:32:00 2013-12-10 12:32:00 2014-01-10 12:32:00\r\n\r\nDOY_5_PREV DOY_CURR DOY_5_NEXT\r\n----------------------- ----------------------- -----------------------\r\n2013-12-05 12:32:00 2013-12-10 12:32:00 2013-12-15 12:32:00\r\n\r\nDAY_PREV DAY_CURR DAY_NEXT\r\n----------------------- ----------------------- -----------------------\r\n2013-12-09 12:32:00 2013-12-10 12:32:00 2013-12-11 12:32:00\r\n\r\nWEEKS_2_PREV WEEKS_CURR WEEKS_2_NEXT\r\n----------------------- ----------------------- -----------------------\r\n2013-11-26 12:32:00 2013-12-10 12:32:00 2013-12-24 12:32:00\r\n\r\n<\/pre>\n<\/p>\nLike 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.<\/p>\n \r\n--\r\n-- DATEADD() - Negative test case for year only.\r\n--\r\n\r\n-- Null Test #1\r\nDECLARE @DT1 DATETIME2 = '2013-12-10 12:32:10.12';\r\nSELECT DATEADD(YY, NULL, @DT1) AS ADD_YEARS_NULL_P2\r\nGO\r\n\r\n-- Null Test #2\r\nSELECT DATEADD(YY, -1, NULL) AS ADD_YEARS_NULL_P3\r\nGO\r\n\r\n-- Empty String Test #1\r\nDECLARE @DT1 DATETIME2 = '2013-12-10 12:32:10.12';\r\nSELECT DATEADD(YY, '', @DT1) AS ADD_YEARS_NULL_P2\r\nGO\r\n\r\n-- Empty String Test #2\r\nSELECT DATEADD(YY, -1, '') AS ADD_YEARS_NULL_P3\r\nGO\r\n<\/pre>\n<\/p>\nThe 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.<\/p>\n \r\noutput: \r\n\r\nMsg 8116, Level 16, State 1, Line 3\r\nArgument data type NULL is invalid for argument 2 of dateadd function.\r\n\r\nADD_YEARS_NULL_P3\r\n-----------------------\r\nNULL\r\n\r\n(1 row(s) affected)\r\n\r\nMsg 8116, Level 16, State 1, Line 4\r\nArgument data type varchar is invalid for argument 2 of dateadd function.\r\n\r\nADD_YEARS_NULL_P3\r\n-----------------------\r\n1899-01-01 00:00:00.000\r\n\r\n<\/pre>\n<\/p>\nTo 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.<\/p>\n","protected":false},"excerpt":{"rendered":" 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…<\/p>\n","protected":false},"author":1,"featured_media":6617,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[814],"tags":[31,987,940,990,15,29],"class_list":["post-6682","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-very-short-articles","tag-database-developer","tag-date-related-parts","tag-date-time-functions","tag-dateadd","tag-john-f-miner-iii","tag-tsql"],"_links":{"self":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts\/6682","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=6682"}],"version-history":[{"count":0,"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts\/6682\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/media\/6617"}],"wp:attachment":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=6682"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=6682"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=6682"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}} |