{"id":6698,"date":"2013-12-03T00:00:16","date_gmt":"2013-12-03T00:00:16","guid":{"rendered":"http:\/\/craftydba.com\/?p=6698"},"modified":"2016-04-20T13:42:44","modified_gmt":"2016-04-20T13:42:44","slug":"dateadd-sub-second-related-functions","status":"publish","type":"post","link":"https:\/\/craftydba.com\/?p=6698","title":{"rendered":"DATEADD() – Sub-second Functions"},"content":{"rendered":"

I am a pertinacious 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 sub-second time offset.<\/p>\n\n\n\n\n\n\n\n
DATE PART<\/td>\nPARAMETER TO USE<\/td>\n<\/tr>\n<\/thead>\n
MILLISCOND<\/td>\nMS<\/td>\n<\/tr>\n
MICROSECOND<\/td>\nMCS<\/td>\n<\/tr>\n
NANASECOND<\/td>\nNS<\/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-- Millisecond\r\nDECLARE @DT1 DATETIME2 = '2013-12-10 12:32:10.1234567';\r\nSELECT \r\n  DATEADD(MS, -1, @DT1) AS MILLISEC_PREV,\r\n  DATEADD(MS, 0, @DT1) AS MILLISEC_CURR,\r\n  DATEADD(MS, 1, @DT1) AS MILLISEC_NEXT\r\nGO\r\n\r\n-- Microsecond\r\nDECLARE @DT1 DATETIME2 = '2013-12-10 12:32:10.1234567';\r\nSELECT \r\n  DATEADD(MCS, -1, @DT1) AS MICROSEC_PREV,\r\n  DATEADD(MCS, 0, @DT1) AS MICROSEC_CURR,\r\n  DATEADD(MCS, 1, @DT1) AS MICROSEC_NEXT\r\nGO\r\n\r\n-- Nanosecond\r\nDECLARE @DT1 DATETIME2 = '2013-12-10 12:32:10.1234567';\r\nSELECT \r\n  DATEADD(NS, -500, @DT1) AS NANOSEC_PREV,\r\n  DATEADD(NS, 0, @DT1) AS NANOSEC_CURR,\r\n  DATEADD(NS, 500, @DT1) AS NANOSEC_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\nMILLISEC_PREV               MILLISEC_CURR               MILLISEC_NEXT\r\n--------------------------- --------------------------- ---------------------------\r\n2013-12-10 12:32:10.1224567 2013-12-10 12:32:10.1234567 2013-12-10 12:32:10.1244567\r\n\r\nMICROSEC_PREV               MICROSEC_CURR               MICROSEC_NEXT\r\n--------------------------- --------------------------- ---------------------------\r\n2013-12-10 12:32:10.1234557 2013-12-10 12:32:10.1234567 2013-12-10 12:32:10.1234577\r\n\r\nNANOSEC_PREV                NANOSEC_CURR                NANOSEC_NEXT\r\n--------------------------- --------------------------- ---------------------------\r\n2013-12-10 12:32:10.1234562 2013-12-10 12:32:10.1234567 2013-12-10 12:32:10.1234572\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 the DATEDIFF()<\/a> function.<\/p>\n","protected":false},"excerpt":{"rendered":"
I am a pertinacious 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…<\/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,989,29],"class_list":["post-6698","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-sub-second-related-parts","tag-tsql"],"_links":{"self":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts\/6698","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=6698"}],"version-history":[{"count":0,"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts\/6698\/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=6698"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=6698"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=6698"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}