How to Add a Month to a Date in SQLite

Problem:

You would like to add one month to a given date in an SQLite database.

Example:

Our database has a table named production with data in the columns id, product, and start_date.

Products have a new production start date that’s one month later than listed above. Let’s get the products’ names and their new start date.

Here is the query you’d write:

Solution:

SELECT product,
  DATE(start_date,'+1 months')
  AS new_start_date
FROM production;

Here’s the result of the query:

Discussion:

Use the SQLite DATE() function to add one month to a given date (or date and time). The arguments required include the date/datetime to change and one or more modifiers with values that indicate how many to units add or subtract.

The first argument can be an expression returning a date/datetime value or a date/datetime column. In our example, it is the column start_date.

The modifiers indicate what (and how much) time unit to add/subtract. In our example, we use the modifier ‘+1 months’. The modifier starts with either plus or minus, indicating addition or subtraction. Then there is a value (e.g. 1) and the unit (e.g. months, years, days, etc.) If you write ‘-2 days’, you subtract 2 days from a given date.

Important: Some modifiers require a slightly different syntax than shown above. For example, the ‘weekday’ modifier comes before the value (i.e. DATE(start_date,'weekday 3')). Other modifiers take no values, like ‘start of month’, which displays the first day of the month for a given date (i.e. DATE(start_date,' start of month')). You can find precise information about date and time modifiers in the SQLite documentation.

We named the new column containing the updated start date new_start_date. The new production start date for the desk is 2019-09-17, one month later than the original date of 2019-08-17. Notice that for 2019-10-31, the new date is 2019-12-01; this is because November has 30 days, not 31.