DATE_ADD()
SQL · Date Functions
Theory
DATE_ADD moves a date forward by a given interval and returns the new date. DATE_ADD('2026-11-05', INTERVAL 30 DAY) gives 2026-12-05. The interval can be days, weeks, months, years, or hours and minutes on timestamps, and the database handles month lengths and leap years for you.
The spelling depends heavily on the database. MySQL and BigQuery use DATE_ADD(date, INTERVAL 30 DAY). SQL Server and Snowflake use DATEADD(day, 30, date), with the unit first. PostgreSQL simply writes date + INTERVAL '30 days'. Oracle adds a number of days with date + 30 and months with ADD_MONTHS. SQLite uses date(order_date, '+30 days'). Adding months has an edge case: 31 January plus one month is 28 February in MySQL, but SQLite rolls over to 3 March, so always test month-end dates in your own database.
Why we use it
Business rules are full of 'X days later': due dates, delivery estimates, trial expiry, warranty end. DATE_ADD does calendar-aware arithmetic, so you never have to count days in a month or worry about leap years yourself.
When to use it
Use it to compute future dates (due date, expiry) or to build a window end. Adding a negative number moves backwards, but DATE_SUB or minus INTERVAL is clearer to read. To get the gap between two dates, use DATEDIFF instead.
How it works
- Start with a date, e.g. order_date
- Choose amount and unit: 30 DAY, 1 MONTH, 2 YEAR
- Database adds it on the calendar
- Past the end of the month? Yes: Apply the month-end rule (clamp or roll over). No: Keep the date.
- Return the new, later date
Syntax
DATE_ADD(date, INTERVAL n unit)Example
SELECT DATE_ADD(hire_date, INTERVAL 30 DAY) FROM employees;Key points
- MySQL syntax shown; Postgres uses date + INTERVAL '30 days'
- SQLite uses date(column, '+30 days')
- SQL Server/Snowflake: DATEADD(day, 30, date) — unit comes first
- Month-end edge case: Jan 31 + 1 month differs between databases; test it
- Works on timestamps too: INTERVAL 2 HOUR, '+90 minutes'