Skip to content
Tutorials Center
My learning

Topics

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.

SQL
-- SQLite
SELECT DATE('2025-02-14', '+30 days');   -- 2025-03-16

Try it

SQL Due dates and trial periods ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

SQL Hired since 2023 ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

Syntax

SQL
-- 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
SQL
-- 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 DATEADD takes the unit as a keyword, not a string; MySQL's INTERVAL takes 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' or make_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() and JULIANDAY().
  • 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.