How to Add (and Subtract) Dates in SQL Server

If you need to add or subtract a time interval from a date in SQL Server, DATEADD() is the function you want. It’s straightforward to use, works with a wide range of date parts, and covers most date arithmetic you’ll ever need.

The Syntax

The syntax goes like this:

DATEADD(datepart, number, date)

• datepart – the unit of time you want to add (day, month, year, etc).
• number – how many units to add. Use a negative number to subtract.
• date – the date value you’re starting from.

Basic Examples

Here are some basic examples to demonstrate some common date additions and subtractions:

DECLARE @d DATE = '2026-03-24';

SELECT
    DATEADD(DAY,   7,  @d)  AS [Plus_7_Days],
    DATEADD(MONTH, 1,  @d)  AS [Plus_1_Month],
    DATEADD(YEAR,  1,  @d)  AS [Plus_1_Year],
    DATEADD(DAY,   -7, @d)  AS [Minus_7_Days],
    DATEADD(MONTH, -3, @d)  AS [Minus_3_Months];

Result:

Plus_7_Days  | Plus_1_Month  | Plus_1_Year  | Minus_7_Days  | Minus_3_Months
-----------------------------------------------------------------------------
2026-03-31   | 2026-04-24    | 2027-03-24   | 2026-03-17    | 2025-12-24

Subtracting is just a matter of passing in a negative number. There’s no separate DATESUBTRACT() function, as DATEADD() can handle both directions.

Supported Date Parts

The first argument controls which unit of time you’re working with. Here are all the supported values:

Datepart	Abbreviations	Description
YEAR	yy, yyyy	Calendar year
QUARTER	qq, q	Quarter (3-month period)
MONTH	mm, m	Calendar month
DAYOFYEAR	dy, y	Day of the year
DAY	dd, d	Calendar day
WEEK	wk, ww	Week
WEEKDAY	dw	Day of the week
HOUR	hh	Hour
MINUTE	mi, n	Minute
SECOND	ss, s	Second
MILLISECOND	ms	Millisecond
MICROSECOND	mcs	Microsecond
NANOSECOND	ns	Nanosecond

For most use cases on dates, you’ll probably only need DAY, MONTH, and YEAR. When working with time values, you’ll probably use HOUR, MINUTE, and SECOND the most. The sub-second options are there for when you’re working with high-precision datetime2 or datetimeoffset values and need that level of granularity.

Adding Time to a DATETIME

DATEADD() works with time components too, not just dates. If your column is a DATETIME or DATETIME2, you can add hours, minutes, or seconds in the same way:

DECLARE @dt DATETIME = '2026-03-24 14:30:00';

SELECT
    DATEADD(HOUR,   2,  @dt)  AS [Plus_2_Hours],
    DATEADD(MINUTE, 45, @dt)  AS [Plus_45_Minutes],
    DATEADD(SECOND, 30, @dt)  AS [Plus_30_Seconds];

Result:

Plus_2_Hours         | Plus_45_Minutes      | Plus_30_Seconds
--------------------------------------------------------------
2026-03-24 16:30:00  | 2026-03-24 15:15:00  | 2026-03-24 14:30:30

Common Uses

Here are a few patterns you’re likely to run into regularly.

Finding records from the last N days:

SELECT * FROM orders
WHERE order_date >= DATEADD(DAY, -30, GETDATE());

Calculating an expiry date:

SELECT
    subscription_start,
    DATEADD(YEAR, 1, subscription_start) AS expiry_date
FROM subscriptions;

Getting the first day of the current month:

SELECT DATEADD(DAY, 1, EOMONTH(GETDATE(), -1)) AS first_of_month;

That last one combines DATEADD() with EOMONTH(), which returns the last day of a given month. Adding one day to the last day of the previous month gives you the first day of the current month.

A Few Things to Watch Out For

• Month-end dates. Adding one month to January 31 gives you February 28 (or 29 in a leap year), not March 3. SQL Server clamps the result to the last valid day of the target month rather than rolling over into the next one. This is usually what you want, but it’s worth being aware of if you’re doing monthly date arithmetic on end-of-month values.
• The return type matches the input type. If you pass in a DATE, you get a DATE back. If you pass in a DATETIME, you get a DATETIME back. This matters if you try to add hours or minutes to a DATE value. SQL Server will throw an error because the DATE type has no time component and the operation is considered invalid. Cast to DATETIME first if you need to do time-based arithmetic on a date-only value.
• The number argument must be an integer. You can’t add 1.5 months or 0.5 days. If you need to add 12 hours, use DATEADD(HOUR, 12, @d) rather than trying to express it as a fractional day.

Combining DATEADD() with DATEDIFF()

One place where DATEADD() and DATEDIFF() work well together is snapping dates to boundaries. Like the start or end of the current month, or the start of the current week. These are common reporting needs and the pattern is worth knowing.

Start and end of the current month:

SELECT
    GETDATE()                                            AS [current_date],
    DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)     AS start_of_month,
    DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 1, 0) AS start_of_next_month;

Result:

current_date              start_of_month            start_of_next_month     
------------------------  ------------------------  ------------------------
2026-03-27 00:51:58.026   2026-03-01 00:00:00.000   2026-04-01 00:00:00.000

The first expression calculates how many complete months have elapsed since day zero (January 1, 1900), then adds that count back to day zero, landing you on the first day of the current month. Adding one more month gives you the first day of next month, which is useful as an exclusive upper bound in a WHERE clause.

Start of the current week:

SELECT
    GETDATE()                                      AS [current_date],
    DATEADD(WEEK, DATEDIFF(WEEK, 0, GETDATE()), 0) AS start_of_week;

Result:

current_date              start_of_week           
------------------------  ------------------------
2026-03-27 00:53:07.366   2026-03-23 00:00:00.000

Basically the same pattern. Here, we’re calculating elapsed weeks since day zero and adding them back to get the Monday that started the current week. Note that day zero (January 1, 1900) was a Monday, which is why this reliably lands on Monday regardless of your DATEFIRST setting.