Here 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;