SQLite DATEADD() Equivalent

SQLite doesn’t have a DATEADD() function like SQL Server (or an ADDDATE() or ADD_DATE() function like in some other DBMSs), but it does have the DATE() function that can make additions to a date value.

SQLite also has a DATETIME() function that can make additions to a datetime value (as well as a TIME() function for time values).

Example

Here’s an example to demonstrate how SQLite’s DATE() function can be used to add ten days to a date:

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

Result:

2050-08-31

And we can add months in the same fashion:

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

Result:

2050-09-21

And it’s the same with years:

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

Result:

2051-08-21

Note that the interval can be specified in its plural or non-plural form. So the following yields the same results as the previous example:

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

Result:

2051-08-21

Hours, Minutes, and Seconds

The DATETIME() function allows us to add hours, minutes and seconds to a given datetime value:

SELECT 
    DATETIME('2050-08-21 00:00:00.000', '+1 hour') AS "Hours",
    DATETIME('2050-08-21 00:00:00.000', '+1 minute') AS "Minutes",
    DATETIME('2050-08-21 00:00:00.000', '+1 second') AS "Seconds";

Result:

Hours                Minutes              Seconds            
-------------------  -------------------  -------------------
2050-08-21 01:00:00  2050-08-21 00:01:00  2050-08-21 00:00:01

And for time values, here’s the TIME() function:

SELECT 
    TIME('00:00:00.000', '+1 hour') AS "Hours",
    TIME('00:00:00.000', '+1 minute') AS "Minutes",
    TIME('00:00:00.000', '+1 second') AS "Seconds";

Result:

Hours     Minutes   Seconds 
--------  --------  --------
01:00:00  00:01:00  00:00:01

Subtract from Dates

We can also use DATE() to subtract:

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

Result:

2050-08-11

All we do is use the - sign in the second argument instead of +.