You would like to display yesterday's date (without time) in an SQL Server database.
SELECT DATEADD(day, -1, CAST(GETDATE() AS date)) AS YesterdayDate;
Assuming today is 2020-09-24, the result is:
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 takes three arguments:
date. Here, the value of
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;