Back to list SQL Server How to Get Yesterday’s Date in T-SQL Database: SQL Server Operators:GETDATE(), CAST(), DATEADD() Problem: You would like to display yesterday's date (without time) in an SQL Server database. Solution: SELECT DATEADD(day, -1, CAST(GETDATE() AS date)) AS YesterdayDate; Assuming today is 2020-09-24, the result is: yesterday_date 2020-09-23 Discussion: To get yesterday's date, you need to subtract one day from today's date. Use GETDATE() to get today's date (the type is datetime) and cast it to date. In SQL Server, you can subtract or add any number of days using the DATEADD() function. The DATEADD() function takes three arguments: datepart, number, and date. Here, the value of datepart is day, because the unit of time you want to subtract is day. The second argument is -1 (you subtract 1 day, which is the same as adding -1 day). The third argument is today's date—the date from which you want to subtract. Of course, you can go back by any interval of time just as easily. Here's an example: SELECT DATEADD(month, -5, CAST(GETDATE() AS date)); An interval of time can also be added to a date. So, here’s a way if you want to get tomorrow's date: SELECT DATEADD(day, 1, CAST(GETDATE() AS date)) AS TomorrowDate; Recommended courses: SQL Basics in SQL Server Common Functions in SQL Server Creating Basic SQL Reports in SQL Server Recommended articles: What's the Difference Between SQL and T-SQL? 24 Rules to the SQL Formatting Standard How to Calculate the Length of a Series with SQL The Most Popular Databases in 2020 How Often Employees Are Running Late for Work: SQL Datetime and Interval SQL Arithmetic See also: How to Add Days to a Date in T-SQL How to Change Date and Time Formats in T-SQL How to Subtract 30 Days from a Date in T-SQL How to Get the Year from a Date in T-SQL How to Get the Month from a Date in T-SQL How to Get the Day from a Date in T-SQL How to Get the Current Date (Without Time) in T-SQL How to Get the Current Date and Time (Without Time Zone) in T-SQL Tags: SQL Server Subscribe to our newsletter Join our weekly newsletter to be notified about the latest posts.