SQL Server DATEADD() Function

The DATEADD() function adds a time/date interval to a date and then returns the date.

Syntax:

DATEADD (datepart , number , datetime_expr)

To add a time measure, the number must be positive. To subtract a time measure, the number must be negative

datepart abbreviations can be used below.

• year, yyyy, yy = Year
• quarter, qq, q = Quarter
• month, mm, m = month
• dayofyear, dy, y = Day of the year
• day, dd, d = Day
• week, ww, wk = Week
• weekday, dw, w = Weekday
• hour, hh = hour
• minute, mi, n = Minute
• second, ss, s = Second
• millisecond, ms = Millisecond

NOTE: DATEADD also accepts abbreviations in the datepart parameter. Use of these abbreviations is generally discouraged as they can be confusing (m vs mi, ww vs w, etc.).

DECLARE @Date DATETIME2 = GETDATE();
SELECT @Date; --2021-05-30 17:01:30.2333333
SELECT DATEADD(YEAR, 1, @Date) --2022-05-30 17:01:30.2333333
SELECT DATEADD(QUARTER, 1, @Date) --2021-08-30 17:01:30.2333333
SELECT DATEADD(WEEK, 1, @Date) --2021-06-06 17:01:30.2333333
SELECT DATEADD(DAY, 1, @Date) --2021-05-31 17:01:30.2333333
SELECT DATEADD(HOUR, 1, @Date) --2021-05-30 18:01:30.2333333
SELECT DATEADD(MINUTE, 1, @Date) --2021-05-30 17:02:30.2333333
SELECT DATEADD(SECOND, 1, @Date) --2021-05-30 17:01:31.2333333
SELECT DATEADD(MILLISECOND, 1, @Date)--2021-05-30 17:01:30.2343333