SQL Server Subtract Days From Date

by

In this comprehensive article, I’ll walk you through multiple approaches to subtract days from dates in SQL Server, complete with real-time examples and best practices.

SQL Server Subtract Days From Date

Before diving into the syntax, let’s consider why this is so essential.

You may wish to calculate payment due dates, determine shipping deadlines, and compute service level agreement (SLA) timeframes, analyzing historical data within specific time windows, etc.

Now, let’s explore the various methods for subtracting days from dates in SQL Server.

Approach 1: Using the DATEADD() Function

The most common and versatile approach to subtract days from a date in SQL Server is using the DATEADD() function. Despite its name suggesting only addition, DATEADD() can also subtract dates by using negative values.

Syntax

DATEADD(datepart, number, date)

Where:

  • datepart: The part of the date you want to modify (day, month, year, etc.)
  • number: The value to add (or subtract when negative)
  • date: The date value to modify

Subtracting Days

Example 1

We can execute the following query to subtract 5 days from the current date.

SELECT DATEADD(day, -5, GETDATE()) AS DateMinus5Days;

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

SQL Server Subtract Days From Date

Example 2

We can use the query below to subtract one day from a specific date.

SELECT DATEADD(day, -1, '2025-06-03') AS PreviousDay;

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

sql subtract days from date

You can subtract any time unit (days, months, years, hours, minutes, etc.) by simply changing the datepart parameter.

Approach 2: Using Date Arithmetic with Integers

SQL Server also allows simple date arithmetic for day manipulation.

Example 1

We can execute the following query to subtract 3 days from the current date using date arithmetic.

SELECT GETDATE() - 3 AS DateMinus3Days;

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

how to subtract days from a date in sql

Example 2

You can execute the query below to subtract 10 days from a specific date.

SELECT CAST('2025-06-03' AS datetime) - 10 AS DateMinus10Days;

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

ms sql subtract days from date

Method 3: Combining DATEADD() with Variables

In real-world applications, we often need to subtract a variable number of days, as shown below.

DECLARE @NumberOfDays int = 14;
DECLARE @StartDate datetime = '2025-06-03';

SELECT DATEADD(day, -@NumberOfDays, @StartDate) AS ResultDate;

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

how to subtract days from date in sql

Common Business Use Cases for Date Subtraction

Example: Finding Sales Orders from the Last 30 Days

SELECT 
    TotalSales,  
    sale_amount, 
    sale_Date
FROM 
    SALES
WHERE 
    sale_Date >= DATEADD(day, -30, GETDATE())
ORDER BY 
    sale_Date DESC;

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

subtract days from a date in sql

Best Practices for Date Subtraction in SQL Server

Here are the top recommendations

  1. Use DATEADD() for clarity: While multiple methods exist, DATEADD() is the most readable and maintainable approach for date manipulation.
  2. Be mindful of data types: Different date and time data types (datetime, date, datetime2) have varying precision and storage requirements.
  3. Watch for date boundaries: When subtracting days, be aware of month and year boundaries that might affect your calculations.
  4. Index date columns correctly: Ensure your date columns are properly indexed if you’ll be querying based on date ranges.

Comparing Different Date Subtraction Methods

Let’s compare the different approaches for subtracting 7 days from the current date:

MethodCode ExampleProsCons
DATEADDDATEADD(day, -7, GETDATE())Clear intent, flexible, works with any date partSlightly more verbose
Date ArithmeticGETDATE() - 7Simple, conciseOnly works for days
DATEADD with VariablesDATEADD(day, -@Days, @Date)Reusable, parameterizedRequires variable declaration

Performance Considerations

When working with large datasets, performance becomes crucial. Here are some insights from my experience optimizing date operations in enterprise environments:

  1. Indexing: Ensure your date columns are properly indexed, especially if you frequently filter on date ranges.
  2. Date Tables: For complex reporting, consider creating a date dimension table that pre-calculates various date attributes.

Conclusion

Subtracting days from dates in SQL Server is a fundamental skill that every database developer should be familiar with. The DATEADD() function provides the most flexible and readable approach, allowing you to subtract any time unit with a simple negative parameter.

You can review all the approaches mentioned in this article and select the one that best suits your requirements.

You may also like the following articles.