In SQLite, we can use the DATE() function to add one or more months to a date.

When we’re dealing with datetime values, we can use the DATETIME() function.

Example

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

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

Result:

2050-09-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 month');

Result:

2050-09-21

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

SELECT 
    DATE('2050-08-21', '+3 month') AS month,
    DATE('2050-08-21', '+3 months') AS months;

Result:

month       months    
----------  ----------
2050-11-21  2050-11-21

Specified in Days

We can alternatively add a month or more to the date based on a number of days:

SELECT DATE('2050-08-21', '+31 days');

Result:

2050-09-21

Although, bear in mind that this will require that you know exactly how many days are appropriate for the months involved. Some months have 30 days, some have 31, and February has either 28 or 29, depending on whether it’s a leap year or not.

With the month/months modifier, SQLite normalises the date, based on the number of days in the relevant month/s.

Here’s a comparison of months vs days when adding to a date in March:

SELECT 
    DATE('2050-03-31', '+1 month') AS "1 month",
    DATE('2050-03-31', '+30 days') AS "30 days",
    DATE('2050-03-31', '+31 days') AS "31 days";

Result:

1 month     30 days     31 days   
----------  ----------  ----------
2050-05-01  2050-04-30  2050-05-01

And here’s the same comparison when adding to a date in September:

SELECT 
    DATE('2050-09-30', '+1 month') AS "1 month",
    DATE('2050-09-30', '+30 days') AS "30 days",
    DATE('2050-09-30', '+31 days') AS "31 days";

Result:

1 month     30 days     31 days   
----------  ----------  ----------
2050-10-30  2050-10-30  2050-10-31

The DATETIME() Function

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

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

Result:

2050-09-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 month');

Result:

2050-09-21 18:30:45