Add Years to a Date in SQLite

In SQLite, we can use the DATE() function to add one or more years to 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:

2051-08-21

If we wanted to subtract the amount, we could replace + with -.

Also, omitting the + part results in the amount being added, as if we’d used +:

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

Result:

2051-08-21

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     
----------  ----------
2055-08-21  2055-08-21

We can alternatively add a year or more to the date 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  
----------  ----------
2051-08-21  2051-08-21

Here’s an example that demonstrates how to use the DATETIME() function to add a year to a datetime value:

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

Result:

2051-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:

2051-08-21 18:30:45