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 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 sub-second time offset.
| DATE PART | PARAMETER TO USE |
| MILLISCOND | MS |
| MICROSECOND | MCS |
| NANASECOND | NS |
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. -- -- Millisecond DECLARE @DT1 DATETIME2 = '2013-12-10 12:32:10.1234567'; SELECT DATEADD(MS, -1, @DT1) AS MILLISEC_PREV, DATEADD(MS, 0, @DT1) AS MILLISEC_CURR, DATEADD(MS, 1, @DT1) AS MILLISEC_NEXT GO -- Microsecond DECLARE @DT1 DATETIME2 = '2013-12-10 12:32:10.1234567'; SELECT DATEADD(MCS, -1, @DT1) AS MICROSEC_PREV, DATEADD(MCS, 0, @DT1) AS MICROSEC_CURR, DATEADD(MCS, 1, @DT1) AS MICROSEC_NEXT GO -- Nanosecond DECLARE @DT1 DATETIME2 = '2013-12-10 12:32:10.1234567'; SELECT DATEADD(NS, -500, @DT1) AS NANOSEC_PREV, DATEADD(NS, 0, @DT1) AS NANOSEC_CURR, DATEADD(NS, 500, @DT1) AS NANOSEC_NEXT GO |
The output from the test is listed below.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
output: MILLISEC_PREV MILLISEC_CURR MILLISEC_NEXT --------------------------- --------------------------- --------------------------- 2013-12-10 12:32:10.1224567 2013-12-10 12:32:10.1234567 2013-12-10 12:32:10.1244567 MICROSEC_PREV MICROSEC_CURR MICROSEC_NEXT --------------------------- --------------------------- --------------------------- 2013-12-10 12:32:10.1234557 2013-12-10 12:32:10.1234567 2013-12-10 12:32:10.1234577 NANOSEC_PREV NANOSEC_CURR NANOSEC_NEXT --------------------------- --------------------------- --------------------------- 2013-12-10 12:32:10.1234562 2013-12-10 12:32:10.1234567 2013-12-10 12:32:10.1234572 |
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() function.
