{"id":6690,"date":"2013-12-02T19:17:33","date_gmt":"2013-12-02T19:17:33","guid":{"rendered":"http:\/\/craftydba.com\/?p=6690"},"modified":"2016-04-20T13:47:02","modified_gmt":"2016-04-20T13:47:02","slug":"dateadd-time-related-functions","status":"publish","type":"post","link":"https:\/\/craftydba.com\/?p=6690","title":{"rendered":"DATEADD() – Time Related Functions"},"content":{"rendered":"

I am a eager beaver in my creation of short articles or tidbits on Transaction SQL date\/time functions<\/a>. <\/p>\n

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. <\/p>\n

Today, I will be exploring the DATEADD()<\/a> function. This function has 13 different input parameters that can be passed to change the resulting output. <\/p>\n

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. <\/p>\n

I will be exploring the three date part parameters that adjust date\/time variable by a fixed time offset.<\/p>\n\n\n\n\n\n\n\n
DATE PART<\/td>\nPARAMETER TO USE<\/td>\n<\/tr>\n<\/thead>\n
HOUR<\/td>\nHH<\/td>\n<\/tr>\n
MINUTE<\/td>\nMI or N<\/td>\n<\/tr>\n
SECOND<\/td>\nSS or S<\/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-- Hours\r\nDECLARE @DT1 SMALLDATETIME = '2013-12-10 12:32:10.12';\r\nSELECT \r\n  DATEADD(HH, -1, @DT1) AS HOUR_PREV,\r\n  DATEADD(HH, 0, @DT1) AS HOUR_CURR,\r\n  DATEADD(HH, 1, @DT1) AS HOUR_NEXT\r\nGO\r\n\r\n-- Minutes\r\nDECLARE @DT1 SMALLDATETIME = '2013-12-10 12:32:10.12';\r\nSELECT \r\n  DATEADD(MI, -1, @DT1) AS MINUTE_PREV,\r\n  DATEADD(MI, 0, @DT1) AS MINUTE_CURR,\r\n  DATEADD(N, 1, @DT1) AS MINUTE_NEXT\r\nGO\r\n\r\n-- Seconds\r\nDECLARE @DT1 DATETIME = '2013-12-10 12:32:10.12';\r\nSELECT \r\n  DATEADD(SS, -1, @DT1) AS SECOND_PREV,\r\n  DATEADD(SS, 0, @DT1) AS SECOND_CURR,\r\n  DATEADD(S, 1, @DT1) AS SECOND_NEXT\r\nGO\r\n\r\n<\/pre>\n<\/p>\n
The output from the test is listed below. <\/p>\n
\r\noutput: \r\n\r\nHOUR_PREV               HOUR_CURR               HOUR_NEXT\r\n----------------------- ----------------------- -----------------------\r\n2013-12-10 11:32:00     2013-12-10 12:32:00     2013-12-10 13:32:00\r\n\r\nMINUTE_PREV             MINUTE_CURR             MINUTE_NEXT\r\n----------------------- ----------------------- -----------------------\r\n2013-12-10 12:31:00     2013-12-10 12:32:00     2013-12-10 12:33:00\r\n\r\nSECOND_PREV             SECOND_CURR             SECOND_NEXT\r\n----------------------- ----------------------- -----------------------\r\n2013-12-10 12:32:09.120 2013-12-10 12:32:10.120 2013-12-10 12:32:11.120\r\n\r\n<\/pre>\n<\/p>\n
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.<\/p>\n","protected":false},"excerpt":{"rendered":"
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,…<\/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,940,990,15,988,29],"class_list":["post-6690","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-very-short-articles","tag-database-developer","tag-date-time-functions","tag-dateadd","tag-john-f-miner-iii","tag-time-related-parts","tag-tsql"],"_links":{"self":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts\/6690","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=6690"}],"version-history":[{"count":0,"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts\/6690\/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=6690"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=6690"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=6690"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}