SQL Reference ยท Date and Time Functions
SQL DATEADD(), DATE_ADD() and INTERVAL
Reference for adding and subtracting time in SQL: DATEADD in SQL Server, DATE_ADD and INTERVAL in MySQL and PostgreSQL, ADD_MONTHS in Oracle and SQLite modifiers.
Adding "30 days" to a date or subtracting "3 months" from a timestamp is one of the most common date jobs, and every database spells it differently. The standard idea is an interval: a length of time you can add to or subtract from a date. PostgreSQL and MySQL support INTERVAL literals; SQL Server uses the function DATEADD(); Oracle adds numbers of days directly and has ADD_MONTHS().
SQLite has none of these. It uses date modifiers such as '+30 days' passed to DATE() or DATETIME(). The examples that run here use that form, and the vendor syntax is in read-only blocks.
-- SQLite SELECT DATE('2025-02-14', '+30 days'); -- 2025-03-16
Try it
Press Run (or Ctrl + Enter) to see the result here.
Press Run (or Ctrl + Enter) to see the result here.
Syntax
-- Standard-style, PostgreSQL and MySQL date_expression + INTERVAL '30 days' -- PostgreSQL DATE_ADD(date_expression, INTERVAL 30 DAY) -- MySQL -- SQL Server DATEADD(day, 30, date_expression) -- Oracle date_expression + 30 ADD_MONTHS(date_expression, 3) -- SQLite DATE(date_expression, '+30 days')
| Part | Description |
|---|---|
| unit | day, week, month, quarter, year, and for timestamps hour, minute, second (names and spelling vary) |
| amount | Number of units; negative numbers move backwards |
| date_expression | The date or timestamp to move |
Returns: a date or timestamp (text in SQLite). Any NULL argument gives NULL.
Dialect differences
| Database | Add 30 days | Subtract 3 months |
|---|---|---|
| SQLite | DATE(d, '+30 days') |
DATE(d, '-3 months') |
| PostgreSQL | d + INTERVAL '30 days' (or d + 30 for a date) |
d - INTERVAL '3 months' |
| MySQL | DATE_ADD(d, INTERVAL 30 DAY) or d + INTERVAL 30 DAY |
DATE_SUB(d, INTERVAL 3 MONTH) |
| SQL Server | DATEADD(day, 30, d) |
DATEADD(month, -3, d) |
| Oracle | d + 30 |
ADD_MONTHS(d, -3) or d - INTERVAL '3' MONTH |
-- PostgreSQL SELECT ordered_at + INTERVAL '14 days' FROM orders; SELECT ordered_at + make_interval(days => 14) FROM orders; -- MySQL SELECT DATE_ADD(ordered_at, INTERVAL 14 DAY) FROM orders; -- SQL Server SELECT DATEADD(day, 14, ordered_at) FROM orders; -- Oracle SELECT ordered_at + 14 FROM orders; SELECT ADD_MONTHS(ordered_at, 1) FROM orders;
Usage notes
- Month ends. PostgreSQL, MySQL, SQL Server and Oracle (
ADD_MONTHS) clamp to the last valid day, so January 31 plus one month is February 28 (or 29). SQLite rolls forward (March 3). See date modifiers. - Units. SQL Server's
DATEADDtakes the unit as a keyword, not a string; MySQL'sINTERVALtakes the number first, then the unit; PostgreSQL's takes one quoted text such as'1 month 2 days'. - Expressions as amounts. In PostgreSQL write
d + n * INTERVAL '1 day'ormake_interval(days => n). SQLite builds the modifier with||:DATE(d, '+' || n || ' days'). - Subtraction of dates gives a length of time, not a date: see
DATEDIFF()andJULIANDAY(). - Stay in the database type. Adding numbers to a MySQL or SQL Server date does not mean days; use the functions above.
- Time zones and daylight saving can make "one day" different from 24 hours in zone-aware types.