Back to list SQLite How to Add a Month to a Date in SQLite Database: SQLite Operators:DATE() 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. idproductstart_date 1desk2019-08-17 2armchair2019-07-17 3sofa2019-10-31 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: idproductnew_start_date 1desk2019-09-17 2armchair2019-08-17 3sofa2019-12-01 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. Recommended courses: SQL Basics Standard SQL Functions SQL Practice Set Recommended articles: 18 Useful Important SQL Functions to Learn ASAP Performing Calculations on Date- and Time-Related Values How Often Employees Are Running Late for Work: SQL Datetime and Interval SQL Arithmetic See also: How to Format a Datetime in SQLite How to Get the Current Time in SQLite How to Get the Current Date in SQLite Tags: SQLite Subscribe to our newsletter Join our weekly newsletter to be notified about the latest posts.