SQL Server Add Days To Date

by

In this comprehensive article, I’ll walk you through multiple approaches to add days to dates in SQL Server with multiple real-time examples and performance considerations.

SQL Server Add Days To Date

Knowing how to add days to dates is necessary while working with SQL Server. Let us discuss various methods to add days to the date in SQL Server.

Method 1: Using the DATEADD Function

The DATEADD function is the most versatile and recommended approach for adding date intervals in SQL Server.

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 (positive) or subtract (negative)
  • date: The starting date value

Example:

The below query will add 30 days to the current date.

SELECT DATEADD(day, 30, GETDATE()) AS DueDate;
SQL Server Add Days To Date

We can use the below query to add 14 days to a specific date.

SELECT DATEADD(day, 14, '2025-04-04') AS FutureDate;
sql server add days to current date

We can use the below query to subtract 7 days from the current date.

SELECT DATEADD(day, -7, GETDATE()) AS PreviousWeek;

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

sql server add 90 days to date

Method 2: Simple Date Arithmetic with Days

You can directly add an integer to a date value. This approach is limited to days only.

Example

The below query is used to add 30 days to the current date.

SELECT GETDATE() + 30 AS DueDate;

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

how to add days in date in sql server

We can use the below query to add 14 days to a specific date.

SELECT '2023-04-15' + 14 AS FutureDate;

We can use the below query to subtract 7 days from the current date.

SELECT GETDATE() - 7 AS LastWeek;

After executing the above query, I got the expected output.

how to add days in date in sql

Method 3: Combining DATEADD with Other Date Functions

For more complex scenarios, we can combine DATEADD with other date functions using the below query.

Adding the Business Days (Excluding Weekends)

CREATE FUNCTION dbo.AddBusinessDays(@StartDate datetime, @DaysToAdd int)
RETURNS datetime
AS
BEGIN
    DECLARE @CurrentDate datetime = @StartDate;
    DECLARE @DaysAdded int = 0;
    
    WHILE @DaysAdded < @DaysToAdd
    BEGIN
        SET @CurrentDate = DATEADD(day, 1, @CurrentDate);
        IF DATEPART(weekday, @CurrentDate) NOT IN (1, 7) -- 1=Sunday, 7=Saturday
            SET @DaysAdded = @DaysAdded + 1;
    END
    
    RETURN @CurrentDate;
END

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

add days to date in sql

Best Practices for Date Manipulation in SQL Server

When working with date additions in the SQL Server, I recommend the following best practices:

  1. While direct addition works for days, DATEADD provides better readability and flexibility for your code.
  2. Always use ISO format (YYYY-MM-DD) for date literals to avoid ambiguity.
  3. When adding days to datetime values, the time component is preserved. Use CAST or CONVERT to truncate time if needed.
  4. For large-scale operations, choose the most efficient method. Direct addition is slightly faster than DATEADD for days, but DATEADD is more flexible.

Real Time Examples for Business Use Cases

Example: Calculating Payment Due Dates

We can use the below query to calculate the payment due date.

SELECT 
    InvoiceID,
    InvoiceDate,
    DATEADD(day, 30, InvoiceDate) AS DueDate,
    CustomerName
FROM Invoices
WHERE DATEADD(day, 30, InvoiceDate) <= GETDATE()
    AND PaymentDate IS NULL;

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

sql server add days to datetime2

Conclusion

Adding days to dates in SQL Server is essential for a database developer. You can utilize the above examples mentioned in this article to achieve this.

You may also like following the articles below.