SQL Server Add 1 Day To Date

by

As a SQL Server developer, I’ve frequently encountered scenarios where adding a single day to a date is necessary. In this comprehensive article, I’ll walk you through every method to add one day to a date in SQL Server, complete with performance considerations.

SQL Server Add 1 Day To Date

Why Date Arithmetic Matters in SQL Server

Before diving into the code, let’s understand why proper date manipulation is crucial in database applications:

  • Data accuracy: Incorrect date calculations can lead to serious business consequences
  • Performance optimization: Choosing the right method can significantly impact query execution time
  • Maintainability: Using SQL Server’s built-in functions improves code readability and reduces errors

Now, let’s explore the various methods to add a day to a date in SQL Server.

Approach 1: Using DATEADD() Function (The Standard Approach)

The DATEADD() function is the most common and straightforward method for date arithmetic in SQL Server. It’s versatile, readable, and performs well in most scenarios.

DECLARE @CurrentDate DATE = '2025-06-18';
SELECT DATEADD(DAY, 1, @CurrentDate) AS NextDay;

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

The DATEADD function follows this syntax:

DATEADD(datepart, number, date)

Where:

  • datepart: The part of the date you want to modify (day, month, year, etc.)
  • number: The amount to add (can be positive or negative)
  • date: The starting date value

The DATEADD function is highly versatile, allowing you to add different date parts as needed:

We can execute the following query to add 1 day.

-- Add 1 day
SELECT DATEADD(DAY, 1, '2025-06-18') AS NextDay;

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

sql add 1 day to date

When to Use DATEADD()

I recommend using DATEADD() as your default approach for date arithmetic because:

  • It clearly communicates your intent to other developers
  • It handles leap years and month boundaries correctly
  • It works with all SQL Server date and time data types

Approach 2: Date Addition Using the + Operator

SQL Server also allows adding days to a date using the plus (+) operator with an integer value:

DECLARE @CurrentDate DATE = '2025-06-20';
SELECT DATEADD(day, +1, CAST(@CurrentDate AS DATE)) AS NextDay

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

add 1 day to date in sql

The + operator works because SQL Server implicitly converts the integer 1 to represent one day when added to a date value. This approach is concise and easy to read for simple day additions.

When to Use the + Operator

In my experience, the + operator is best suited for:

  • Simple scripts where brevity is valued
  • Situations where you’re only adding whole days (not hours, minutes, etc.)
  • Code that needs to be highly readable at a glance

However, I generally prefer DATEADD() for production code because it makes the intention clearer and provides more flexibility.

Approach 3: Using EOMONTH()

If you need to calculate “tomorrow” relative to the end of a month (e.g., getting the first day of the next month), the EOMONTH() function can be helpful:

DECLARE @CurrentDate DATE = '2025-06-30';
SELECT DATEADD(DAY, 1, EOMONTH(@CurrentDate)) AS FirstDayOfNextMonth;

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

add 1 day to date in sql server

The EOMONTH() function returns the last day of the month for a specified date. By adding one day to this result, you get the first day of the following month.

When to Use EOMONTH()

This approach is particularly useful for:

  • Financial applications that need to calculate month boundaries
  • Reporting systems that aggregate data by month
  • Scheduling processes that run on the first day of each month

Approach 4: Using DATEADD() with DATEDIFF() for Date Normalization

Sometimes you need to add a day while also normalizing the time component to midnight. This combination of functions handles that scenario:

DECLARE @CurrentDateTime DATETIME = '2025-06-18 14:30:25';
SELECT DATEADD(DAY, 1, DATEADD(DAY, DATEDIFF(DAY, 0, @CurrentDateTime), 0)) AS NextDayMidnight;

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

how to add 1 day to date in sql query

The inner DATEDIFF and DATEADD operations normalize the current date to midnight, and then the outer DATEADD adds one day to that normalized date.

When to Use This Combined Approach

I’ve found this pattern particularly valuable in:

  • ETL processes that need to compare dates without time components
  • Applications where consistency in time values is important
  • Situations where you’re working with DATETIME values but only care about the date portion

Approach 5: Using Date Literals with Arithmetic

For date-only operations in newer versions of SQL Server, you can use date literals combined with arithmetic:

SELECT DATE '2025-06-20' + 1 AS NextDay;

This returns 2025-06-21.

This approach uses the ANSI SQL standard date literal syntax, which provides good readability and clarity about the data type.

When to Use Date Literals

Date literals are best for:

  • Adhering to ANSI SQL standards for better cross-platform compatibility
  • Making it clear that you’re working with DATE (not DATETIME) values
  • Modern SQL Server applications (SQL Server 2012 and later)

Performance Comparison of Date Addition Methods

Based on my experience optimizing SQL Server performance for large-scale systems, here’s how these methods compare:

MethodPerformanceFlexibilityReadabilityBest For
DATEADD()ExcellentHighExcellentMost situations
+ OperatorExcellentLowGoodSimple scripts, day-only additions
EOMONTH() + 1GoodMediumGoodMonth boundary calculations
DATEADD() + DATEDIFF()GoodMediumFairTime normalization scenarios
Date LiteralsExcellentLowExcellentModern, standards-compliant code
Custom FunctionFairHighGoodBusiness day calculations
Calendar TableExcellentHighestExcellentEnterprise applications

Real-World Applications

Here are some practical examples of when you might need to add a day to a date in SQL Server:

Payment Scheduling

-- Set payment due date to the next day
INSERT INTO Invoices (CustomerID, Amount, IssueDate, DueDate)
VALUES (
    12345, 
    299.99, 
    GETDATE(), 
    DATEADD(DAY, 1, GETDATE())
);

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

sql add day to date without dateadd

Appointment Scheduling

-- Schedule follow-up appointment for next day
INSERT INTO Appointments (PatientID, AppointmentDate, Purpose)
VALUES (
    98765,
    DATEADD(DAY, 1, @InitialVisitDate),
    'Follow-up Consultation'
);

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

sql server add one day to date

Video Tutorial

Conclusion

After working with SQL Server date manipulation. I’ve found that:

  1. For general use, the DATEADD() function offers the best combination of clarity and flexibility
  2. For simple scripts, the + operator is concise and effective.

You may also like the following articles.