Calculating Past Dates in SQL Server

Working with dates is one of those things you’ll do constantly in SQL Server, and knowing how to calculate past dates efficiently can save you a ton of time. Whether you’re pulling last month’s sales data or filtering records from the past week, SQL Server gives you several straightforward ways to handle date calculations.

The `DATEADD()` Function

The workhorse function for date calculations in SQL Server is DATEADD(). Although it’s name implies adding to dates, you can use it to subtract from dates too. It’s easy to use and should be able to handle most scenarios you encounter.

The function takes three arguments:

The date part you want to modify (days, months, years, etc.)
The number to add (use negative numbers to go backwards)
The starting date.

Like this:

DATEADD (datepart , number , date )

Quick Example

Here’s a quick example to demonstrate how to call the function:

SELECT DATEADD(DAY, -7, GETDATE()) AS OneWeekAgo

This example subtracts 7 days from the current date and time. You can swap out DAY for other date parts like WEEK, MONTH, YEAR, HOUR, or MINUTE depending on what you need. The function is smart about things like month lengths and leap years, so you don’t have to worry about the math getting weird on edge cases as long as you use the appropriate datepart (like MONTH for months, YEAR for years, etc).

Common Past Date Calculations

Here are some calculations you’ll probably use all the time:

Description	SQL Expression
Yesterday	`DATEADD(DAY, -1, GETDATE())`
Last week (7 days ago)	`DATEADD(DAY, -7, GETDATE())`
Last week (1 week ago)	`DATEADD(WEEK, -1, GETDATE())`
Last month	`DATEADD(MONTH, -1, GETDATE())`
Last year	`DATEADD(YEAR, -1, GETDATE())`
90 days ago	`DATEADD(DAY, -90, GETDATE())`

For more precise values, you could use SYSDATETIME() instead of GETDATE() since it returns more fractional seconds precision, but this will usually only matter if you’re also dealing with the time part.

Getting the Start of Time Periods

Sometimes you need the first day of last month or the beginning of last year, not just “30 days ago.” This requires a bit more finesse. To get the first day of the current month, you can combine DATEADD() with DATEDIFF():

SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0) AS FirstDayThisMonth

This looks a bit cryptic at first, but here’s what’s happening:

DATEDIFF(MONTH, 0, GETDATE()) counts the number of months between SQL Server’s zero date (1900-01-01) and today.
Then DATEADD() adds that many months back to the zero date, effectively rounding down to the first of the current month.

To get the first day of last month, just subtract one more month:

SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 1, 0) AS FirstDayLastMonth

The same technique works for years, quarters, and other date parts.

From SQL Server 2022, you can use the DATETRUNC() function to get the start of the month:

SELECT DATETRUNC(MONTH, GETDATE());

That returns the start of the current month. To get the start of last month we can do this:

SELECT DATEADD(MONTH, -1, DATETRUNC(MONTH, GETDATE()));

Another way to do some of this stuff is with the EOMONTH() function.

Using `EOMONTH()` for Month Calculations

If you’re working with months specifically, EOMONTH() can be a handy function. It returns the last day of a month, which makes certain calculations much cleaner:

SELECT EOMONTH(GETDATE()) AS LastDayOfThisMonth

That returns the last day of the current month.

To get the last day of last month:

SELECT EOMONTH(GETDATE(), -1) AS LastDayOfPreviousMonth

The (optional) second parameter tells it how many months to offset. Use negative numbers for past months. You can combine this with DATEADD() to get the first day of the previous month too:

SELECT DATEADD(DAY, 1, EOMONTH(GETDATE(), -2)) AS FirstDayOfPreviousMonth

Filtering Records with Past Dates

When you’re filtering data in a WHERE clause, it’s usually better to calculate your date boundary once rather than calculating dates for every row. Here’s an example that finds all orders from the last 30 days:

DECLARE @ThirtyDaysAgo DATE = DATEADD(DAY, -30, GETDATE());

SELECT OrderID, OrderDate, TotalAmount
FROM Orders
WHERE OrderDate >= @ThirtyDaysAgo

This approach is more efficient than calculating the date boundary in the WHERE clause itself because SQL Server calculates the date once and can use indexes properly (which makes it a SARGable query). If you put the calculation in the WHERE clause on the column itself (like WHERE DATEADD(DAY, 30, OrderDate) >= GETDATE()), it can’t use indexes effectively.

Removing Time Components

One thing about the GETDATE() function is that it includes the time component. If you’re only comparing dates and want to ignore the time, convert to DATE:

SELECT CAST(GETDATE() AS DATE) AS TodayWithoutTime

Or use the CONVERT() function with a specific style. This can be useful when you’re doing comparisons like “orders from yesterday”, because you want the full 24-hour period, rather than a specific time frame within that period.

Working with Business Days

Calculating business days (excluding weekends) requires a bit more logic since there’s no built-in function for this. You’ll typically use a calendar table or write a function that checks DATEPART(WEEKDAY, date) values. But for simple cases, you can use a recursive CTE to count back a certain number of weekdays:

WITH DateSequence AS (
    SELECT GETDATE() AS DateValue, 0 AS BusinessDaysBack
    UNION ALL
    SELECT DATEADD(DAY, -1, DateValue),
           CASE WHEN DATEPART(WEEKDAY, DATEADD(DAY, -1, DateValue)) NOT IN (1, 7)
                THEN BusinessDaysBack + 1
                ELSE BusinessDaysBack
           END
    FROM DateSequence
    WHERE BusinessDaysBack < 10
)
SELECT DateValue FROM DateSequence WHERE BusinessDaysBack = 10;

This finds the date that was 10 business days ago. It’s not the most elegant solution for complex scenarios, but it works.

The DATEADD() Function