Add Month To Date SQL Server

by

In this comprehensive article, I’ll walk you through multiple approaches to add months to date in SQL Server, complete with real-world examples. You’ll learn not only the syntax but also the performance implications and best practices for each method.

Add Month To Date SQL Server

Let’s take a deep dive into all the possible approaches to achieve this.

Approach 1: Using the DATEADD Function

The most straightforward and efficient way to add months to a date in SQL Server is by using the DATEADD function. This powerful function allows you to add specific time intervals to a date value, including months.

Syntax

The basic syntax for adding months to a date is:

DATEADD(month, number_of_months, date)

Where:

  • month is the datepart specifying we want to add months
  • number_of_months is the integer value of months to add (can be positive or negative)
  • date is your starting date

Examples

Let’s start with some basic examples.

Example 1

We can use the query below to add one month to the current date.

SELECT CURRENT_TIMESTAMP AS CurrentDate,
       DATEADD(month, 1, CURRENT_TIMESTAMP) AS DatePlus1Month;

After executing the above query, I got the expected output as shown in the screenshot below.

Add Month To Date SQL Server
Example 2

We can use the query below to add 3 months to a specific date.

SELECT DATEADD(month, 3, '2023-06-24') AS DatePlus3Months;

After executing the above query, I obtained the expected output, as shown in the screenshot below.

add months to date sql server
Example 3
Working with Month Addition and End-of-Month Dates

One critical consideration when adding months to dates is how SQL Server handles month-end dates. This issue frequently arises when building financial systems, as I discovered while implementing a loan management system for a bank in Chicago.

How SQL Server Handles Month Boundaries

When you add months to a date that falls on the last day of the month, SQL Server intelligently adjusts the result to the last day of the target month.

For example:

-- January 31 + 1 month = February 28/29 (last day of February)
SELECT DATEADD(month, 1, '2023-01-31') AS OneMonthLater;

After executing the above query, I got the expected output as per the below screenshot.

sql server add month to date

This behavior is particularly useful for financial calculations, where end-of-month dates are often significant.

Approach 2: Using EOMONTH Function with Month Addition

For applications where you specifically need the last day of a month after adding months, SQL Server offers the EOMONTH function combined with DATEADD.

Syntax

The syntax when combined with month addition is:

EOMONTH(start_date, months_to_add)

This returns the last day of the month, offset by the specified number of months from the start date.

Examples

Example 1

We can execute the below query to get the last day of the current month.

SELECT EOMONTH(GETDATE(), 0) AS CurrentMonthEnd;

After executing the above query, I got the expected output as per the below screenshot.

sql add month to date
Example 2

We can execute the below query to get the last day of the month 3 months from now.

SELECT EOMONTH(GETDATE(), 3) AS ThreeMonthsLaterMonthEnd;

After executing the above query, I got the expected output as shown in the below screenshot.

add month to date sql

This approach ensures billing always occurs on the last day of appropriate months, regardless of how many days each month contains.

Calculating Date Ranges with Month Addition

One common business requirement I’ve implemented for retail clients in New York involves calculating date ranges spanning multiple months for reporting purposes.

Creating Month-Based Date Ranges

We can execute the below query to generate monthly boundaries:

-- Create a 3-month date range for quarterly reporting
DECLARE @StartDate date = '2025-01-01';

SELECT
    DATEADD(month, n.n, @StartDate) AS PeriodStartDate,
    DATEADD(day, -1, DATEADD(month, n.n + 1, @StartDate)) AS PeriodEndDate
FROM 
    (VALUES (0),(1),(2)) AS n(n);

This query generates three monthly periods with properly calculated start and end dates. After executing the above query, I got the expected output as per the below screenshot.

how to add months to a date in sql

Performance Comparison of Different Methods

Based on my benchmarking for a large insurance client in Houston processing millions of policy records, here are the relative performance characteristics:

MethodRelative PerformanceBest Use Case
DATEADDFastestStandard month addition in most scenarios
EOMONTHFastWhen you specifically need month-end dates
Custom FunctionsSlowerComplex business rules for date calculation

Conclusion

Knowing the month addition in SQL Server is an essential skill for any database developer or SQL professional.

To summarize the key takeaways:

  1. Use DATEADD for straightforward month addition – It’s the most efficient and readable approach for most scenarios
  2. Leverage EOMONTH when you specifically need month-end dates – Particularly useful for financial applications
  3. Be mindful of how SQL Server handles month boundaries – The platform intelligently adjusts dates when adding months would create an invalid date
  4. Consider performance implications for large datasets – Proper indexing strategies can dramatically improve query performance
  5. Create reusable functions for complex business logic – When standard functions don’t meet your specific requirements

You may also like the following articles