Substract days to date in SQL

This is how to substract a number of days to a date, including today, for several SQL dialects.

Get today date

To get the current date, most SQL dialects use CURRENT_DATE.

You can also get the current timestamp:

SQL Dialect Function for current time
PostgresSQL GETDATE()
Redshift NOW()
BigQuery CURRENT_TIMESTAMP()
MySQL NOW()

Substract days to current date

To substract days to current date, for example to get yesterday’s date, it will depend on the SQL dialect.

Redshift and PostgreSQL:

-- Method 1
SELECT CURRENT_DATE - INTERVAL '1 day';

-- Method 2
SELECT DATEADD('day', -1, CURRENT_DATE)

MySQL:

-- Method 1
SELECT CURRENT_DATE - INTERVAL '1 day';

-- Method 2
SELECT DATE_SUB(CURRENT_DATE, INTERVAL 1 DAY);

BigQuery:

SELECT CURRENT_DATE - 1 day;