Subtract Years from a Date in SQLite

In SQLite, we can use the DATE() function to subtract one or more years from a date.

For datetime values, we can use the DATETIME() function.

Example

Here’s an example that uses the DATE() function:

SELECT DATE('2050-08-21', '-1 year');

Result:

2049-08-21

If we wanted to add the amount, we could replace - with +, or omit it altogether.

We can specify the years in plural or non-plural form. In other words, year is equivalent to years:

SELECT 
    DATE('2050-08-21', '-5 year') AS year,
    DATE('2050-08-21', '-5 years') AS years;

Result:

year        years     
----------  ----------
2045-08-21  2045-08-21

We can alternatively subtract years based on a number of months or days:

SELECT 
    DATE('2050-08-21', '-12 months') AS "12 Months",
    DATE('2050-08-21', '-365 days') AS "365 Days";

Result:

12 Months   365 Days  
----------  ----------
2049-08-21  2049-08-21

This example uses the DATETIME() function to do the same thing:

SELECT DATETIME('2050-08-21', '-1 year');

Result:

2049-08-21 00:00:00

In this case, I passed a date value, but the function returned a datetime value.

Here’s another example, this time with a datetime value:

SELECT DATETIME('2050-08-21 18:30:45', '-1 year');

Result:

2049-08-21 18:30:45