How to Add Days and Months to Dates in SQL Server

by

When working with SQL Server, you may often find yourself having to shift a date by a certain number of days or months. While it sounds simple, the right function and approach can save you from subtle bugs and errors, especially when dealing with edge cases like leap years or month-end rollovers.

Let’s take a look at how to add days and months to dates in SQL Server.

The DATEADD() Function

SQL Server’s built-in tool for date arithmetic is the DATEADD() function. Its basic syntax is:

DATEADD(datepart, number, date)
  • datepart: the unit of time you want to add (like day or month).
  • number: how many units to add (can be positive or negative).
  • date: the base date you’re modifying.

This works with most date and time data types, including date, datetime, and datetime2.

Adding Days to a Date

The simplest case is adding days: 

SELECT DATEADD(day, 5, '2023-05-10');

Output:

2023-05-15 00:00:00.000

You can also use a negative number to subtract days:

SELECT DATEADD(day, -7, '2023-05-10');

Output:

2023-05-03 00:00:00.000

The return type depends on the input. When using a string literal date like in the above examples, it returns a datetime value. But if we’d provided it using a valid date/time value, then it would use that data type.

For example:

DECLARE @date AS DATE = '2023-05-10';
SELECT DATEADD(day, 5, @date);

Output:

2023-05-15

We can use it against columns too:

SELECT 
    OrderDate,
    DATEADD(day, 5, OrderDate) AS ShippingDate
FROM Orders;

Because SQL Server understands calendar math, it doesn’t matter if the date crosses month or year boundaries. For example, adding 30 days to 2023-12-15 correctly lands you in January of the next year.

Adding Months to a Date

The syntax is the same when adding other date parts, such as months:

DECLARE @date AS DATE = '2023-01-31';
SELECT 
    DATEADD(month, 1, @date) AS "1_month_later",
    DATEADD(month, 2, @date) AS "2_months_later",
    DATEADD(month, 3, @date) AS "3_months_later";

Output:

1_month_later    2_months_later   3_months_later  
---------------- ---------------- ----------------
      2023-02-28       2023-03-31       2023-04-30

SQL Server knows which date is the end of each month, and so it simply returns the last day of the month. This means that we could get a different result than if we’d added a certain number of days to the date. Here’s what happens if we add 30, 60, and 90 days to the date:

DECLARE @date AS DATE = '2023-01-31';
SELECT 
    DATEADD(day, 30, @date) AS "30_days_later",
    DATEADD(day, 60, @date) AS "60_days_later",
    DATEADD(day, 90, @date) AS "90_days_later";

Output:

30_days_later    60_days_later    90_days_later   
---------------- ---------------- ----------------
      2023-03-02       2023-04-01       2023-05-01

So, the month argument is handy when you want to keep dates aligned with “end of month” patterns.

Leap Years

SQL Server knows about leap years, so there’s no need to worry about it messing up the date due to a leap year:

SELECT 
    DATEADD(day, 1, '2024-02-28') AS leap_year,
    DATEADD(day, 1, '2025-02-28') AS non_leap_year;

Output:

leap_year               non_leap_year          
----------------------- -----------------------
2024-02-29 00:00:00.000 2025-03-01 00:00:00.000

Common Use Cases

There are many reasons why you might want to add days to a date in SQL Server. Here are some of the more common use cases.

Renewal Dates

Add 12 months to a subscription date: 

DECLARE @SubscriptionStartDate AS DATE = '2025-05-10';
SELECT 
    @SubscriptionStartDate AS SubscriptionStarts,
    DATEADD(month, 12, @SubscriptionStartDate) AS SubscriptionEnds;

Result:

SubscriptionStarts SubscriptionEnds
------------------ ----------------
        2025-05-10       2026-05-10

Rolling 30-day Window

Find records from the past 30 days: 

SELECT 
    * 
FROM 
    Sales 
WHERE 
    SaleDate >= DATEADD(day, -30, GETDATE());

End-of-month Logic

If you need to snap to the last day of the month after adding months, you can combine DATEADD() with EOMONTH():

SELECT EOMONTH(DATEADD(month, 1, '2023-01-15'));

Output:

2023-02-28

Summary

  • Use DATEADD() with dateparts like day or month for reliable date arithmetic.
  • SQL Server adjusts intelligently for months with fewer days.
  • Negative values work just as well for subtraction.
  • EOMONTH() is a handy companion when dealing with month-end calculations.

Date math in SQL Server doesn’t need to be messy. Once you know how DATEADD() behaves with days and months, you can handle most scenarios cleanly without writing complex logic.