SQL current date minus 1 day, month or year (GETDATE)

In SQL Server, subtract time with DATEADD and a negative number. T-SQL has no DATESUB:

SELECT DATEADD(day,    -1, GETDATE());  -- now minus 1 day
SELECT DATEADD(month,  -1, GETDATE());  -- now minus 1 month
SELECT DATEADD(year,   -1, GETDATE());  -- now minus 1 year
SELECT DATEADD(minute, -30, GETDATE()); -- now minus 30 minutes

The syntax is DATEADD(datepart, number, date) (Microsoft docs). A positive number moves forward: DATEADD(month, 5, GETDATE()) is five months from now.

Date minus date is DATEDIFF(day, @from, @to): the number of days between them. It counts boundaries crossed, not whole periods, so DATEDIFF(year, '20251231', '20260101') returns 1 after a single day.

Current date minus 1 day, without the time

GETDATE() includes the time. For yesterday's date, cast to date first:

-- today, no time part
SELECT CAST(GETDATE() AS date);

-- yesterday
SELECT DATEADD(day, -1, CAST(GETDATE() AS date));

-- yesterday, SQL Server 2025
SELECT DATEADD(day, -1, CURRENT_DATE);

Since SQL Server 2025, T-SQL has CURRENT_DATE: it does the same as CAST(GETDATE() AS date) and returns date (Microsoft docs). Azure SQL Database and Azure SQL Managed Instance have it too. SQL Server 2022 and older don't have it: 2022 fails with "Incorrect syntax near the keyword 'CURRENT_DATE'".

DATEADD returns the type it was given: datetime for GETDATE(), date for a date. A string literal becomes datetime, so write it as 'yyyymmdd': with a Russian, German or British session language, '2026-11-03' reads as March 11.

GETDATE minus 1 day in a WHERE clause

The last 24 hours:

SELECT * FROM Orders
WHERE CreatedAt >= DATEADD(day, -1, GETDATE());

Yesterday as a calendar day, from midnight to midnight:

SELECT * FROM Orders
WHERE CreatedAt >= DATEADD(day, -1, CAST(GETDATE() AS date))
  AND CreatedAt <  CAST(GETDATE() AS date);

Keep the column bare and do the math on the other side. I compared plans on SQL Server 2022 with an index on CreatedAt: this range gets an Index Seek, while DATEDIFF(day, CreatedAt, GETDATE()) = 1 or CONVERT(varchar, CreatedAt, 120) scan the whole index. CAST(CreatedAt AS date) = ... happens to seek too, but the half-open range >= start AND < end works the same in every database.

With a DECLARE @today date variable the index may go unused: the optimizer builds the plan without knowing its value and assumes a fixed share of the table matches. In my test, SELECT * with the variable read the whole clustered index. If you need the variable, add OPTION (RECOMPILE).

Don't end the day with BETWEEN ... AND '23:59:59.999': against a datetime column, '23:59:59.999' rounds up to the next midnight, so rows stamped exactly at today's 00:00:00 slip in.

Current date minus 1 year or 1 month

-- same day last year
SELECT DATEADD(year, -1, CAST(GETDATE() AS date));

SELECT DATEADD(month, -1, '20260331'); -- 2026-02-28 00:00:00.000
SELECT DATEADD(year, -1, '20280229');  -- 2027-02-28 00:00:00.000

If that day doesn't exist in the target month, you get the month's last day.

SQL Server 2022 added DATETRUNC for the start of a month:

-- start of this month
SELECT DATETRUNC(month, GETDATE());

-- start of last month
SELECT DATEADD(month, -1, DATETRUNC(month, GETDATE()));

-- last day of last month
SELECT EOMONTH(GETDATE(), -1);

On older versions, use the DATEADD + DATEDIFF trick from start of month in MS SQL.

GETDATE, SYSDATETIME or CURRENT_TIMESTAMP

• CURRENT_TIMESTAMP is the ANSI name for GETDATE(): same datetime, accurate to about 3 ms. Current timestamp minus 1 day is DATEADD(day, -1, CURRENT_TIMESTAMP).
• SYSDATETIME() returns datetime2(7) with seven fractional digits.
• GETUTCDATE() and SYSUTCDATETIME() return UTC. Azure SQL Database runs on UTC by default, so there GETDATE() is UTC too.

Datepart values

• year (yy, yyyy)
• quarter (qq, q)
• month (mm, m)
• dayofyear (dy, y)
• day (dd, d)
• week (wk, ww)
• weekday (dw, w)
• hour (hh)
• minute (mi, n)
• second (ss, s)
• millisecond (ms)
• microsecond (mcs)
• nanosecond (ns)

In DATEADD, dayofyear, day and weekday all add days. microsecond and nanosecond don't work with datetime values like GETDATE().

Current date minus 1 day in PostgreSQL, MySQL, SQLite and Oracle

I ran these on PostgreSQL 16, MySQL 8.4 and SQLite 3.51 and checked Oracle against its documentation.

PostgreSQL

SELECT CURRENT_DATE - 1;                  -- today minus 1 day
SELECT now() - interval '1 day';          -- now minus 1 day
SELECT CURRENT_DATE - interval '1 month'; -- today minus 1 month

Date minus interval is a timestamp: CURRENT_DATE - INTERVAL '1 day' is yesterday 00:00:00. Add ::date for a date.

MySQL

SELECT DATE_SUB(CURDATE(), INTERVAL 1 DAY);   -- today minus 1 day
SELECT NOW() - INTERVAL 1 DAY;                -- now minus 1 day
SELECT DATE_SUB(CURDATE(), INTERVAL 1 MONTH); -- today minus 1 month

Don't write CURDATE() - 1: MySQL turns the date into a number, and on November 1 you get 20261100.

SQLite

SELECT date('now', '-1 day');     -- today minus 1 day
SELECT datetime('now', '-1 day'); -- now minus 1 day
SELECT date('now', '-1 month');   -- today minus 1 month

'now' is UTC; add 'localtime' for local time. date('2026-03-31', '-1 month') returns 2026-03-03. Since 3.46, add 'floor' as the last argument to get 2026-02-28.

Oracle

SELECT TRUNC(SYSDATE) - 1 FROM dual;             -- today minus 1 day
SELECT SYSDATE - 1 FROM dual;                    -- now minus 1 day
SELECT ADD_MONTHS(TRUNC(SYSDATE), -1) FROM dual; -- today minus 1 month

A number means days, so SYSDATE - 1/24 is an hour ago. For years use ADD_MONTHS(SYSDATE, -12). SYSDATE - INTERVAL '1' MONTH raises an error on March 31, because February 31 doesn't exist.

Pitfalls in T-SQL

• GETDATE() - 1 works on datetime, but SYSDATETIME() - 1 fails with "Operand type clash: datetime2 is incompatible with int". Use DATEADD.
• CURRENT_DATE - 1 works in PostgreSQL, but T-SQL doesn't add numbers to a date: in SQL Server 2025 it fails with "Operand type clash: date is incompatible with int". Write DATEADD(day, -1, CURRENT_DATE).
• m is month, not minute. Minute is mi or n.
• The datepart can't be a variable: DATEADD(@part, -1, GETDATE()) fails with "Invalid parameter 1 specified for dateadd". Branch with CASE.