SQL Server Get Yesterday's Date

As a database developer, recently, I got the requirement to retrieve yesterday’s date in SQL Server for one of the reporting projects. This article will walk you through multiple approaches to get yesterday’s date in SQL Server.

Table of Contents

SQL Server Get Yesterday’s Date

There are many common scenarios where retrieving the date from yesterday is essential for a developer working with SQL Server. Now, let’s explore the various methods to retrieve yesterday’s date in SQL Server.

Method 1: Using DATEADD() Function

The most common and straightforward approach is to use the DATEADD() function with GETDATE(). This function allows you to add or subtract a specified time interval from a date. This will return yesterday’s date with the current time.

SELECT DATEADD(DAY, -1, GETDATE()) AS YesterdayWithTime

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

However, if you need just the date without the time component, you can cast it to the date type using the query below.

SELECT CAST(DATEADD(DAY, -1, GETDATE()) AS DATE) AS YesterdayDateOnly

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

Pros of the DATEADD Method:

Simple and easy to understand
Works across all versions of SQL Server
Can be easily modified to get other dates (two days ago, last week, etc.)

Cons of the DATEADD Method:

Requires type casting if you only need the date portion

Method 2: Using DATEADD() with GETUTCDATE()

If your application needs to work with UTC time rather than the server’s local time, you can use GETUTCDATE() instead, use the below query.

SELECT DATEADD(DAY, -1, GETUTCDATE()) AS YesterdayUTC
SELECT CAST(DATEADD(DAY, -1, GETUTCDATE()) AS DATE) AS YesterdayUTCDateOnly

This ensures consistent date calculations regardless of the server’s time zone, which is particularly important for applications serving users across different time zones in the United States.

Method 3: Finding Yesterday’s Midnight

Sometimes you need to capture all data from yesterday, meaning from yesterday at 00:00:00 to yesterday at 23:59:59. Here’s how to get the start of yesterday:

SELECT DATEADD(DAY, DATEDIFF(DAY, 1, GETDATE()), 0) AS YesterdayStartTime

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

The below query gives you yesterday 23:59:59.997, which is typically adequate for capturing all of yesterday’s data.

SELECT DATEADD(MILLISECOND, -3, DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)) AS YesterdayEndTime

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

You can also use:

SELECT DATEADD(day, -1, GETDATE()) AS YesterdayWithTime
SELECT DATEADD(day, -1, CAST(GETDATE() AS DATE)) AS YesterdayAt00_00_00

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

Method 4: Using Date Literals and Arithmetic

You can use the below query.

SELECT CURRENT_TIMESTAMP - 1 AS YesterdayWithTime

After the query execution, I got the expected output.

Method 5: Using Date Functions in Stored Procedures

When working with stored procedures that need to process yesterday’s data, you can create parameters that automatically calculate yesterday’s date using the query below.

CREATE PROCEDURE sp_GetYesterdaySalesData
AS
BEGIN
    DECLARE @YesterdayDate DATE = CAST(DATEADD(DAY, -1, GETDATE()) AS DATE)
    
    SELECT * FROM Sales
    WHERE sale_date = @YesterdayDate
END

The command was executed and the stored procedure was created successfully, as shown in the screenshot below.

how to get previous date from current date in sql

After executing the stored procedure, I got the expected output as shown in the screenshot below.

While working with this requirement, I encountered the Operand Type Clash: Date Is Incompatible With Int error.

Conclusion

Getting yesterday’s date in SQL Server is so easy using the above approaches, as mentioned in this article. The DATEADD(DAY, -1, GETDATE()) Function combined with appropriate casting is the most common and efficient approach, in my opinion.

You may also like the following articles.

Bijay Kumar Sahoo

After working for more than 15 years in the Software field, especially in Microsoft technologies, I have decided to share my expert knowledge of SQL Server. Check out all the SQL Server and related database tutorials I have shared here. Most of the readers are from countries like the United States of America, the United Kingdom, New Zealand, Australia, Canada, etc. I am also a Microsoft MVP. Check out more here.