Adding or subtracting dates is a common task in SQL—whether you’re calculating invoice due dates, project deadlines, or renewal cycles. But the exact syntax depends on your database system. MySQL, PostgreSQL, and SQL Server all support date arithmetic, but each in their own way. In this guide, we’ll explore how to add and subtract dates using real-world examples and best practices in each platform.
MySQL: Using DATE_ADD and DATE_SUB
MySQL provides DATE_ADD() and DATE_SUB() functions for date calculations.
Add 30 days to a specific date:
SELECT DATE_ADD('2024-10-01', INTERVAL 30 DAY);
-- Output: '2024-10-31'
Subtract days using DATE_SUB:
SELECT DATE_SUB('2024-10-01', INTERVAL 15 DAY);
-- Output: '2024-09-16'
Or use DATE_ADD with a negative interval:
SELECT DATE_ADD('2024-10-01', INTERVAL -15 DAY);
If NULL is passed as the date input:
SELECT DATE_ADD(NULL, INTERVAL 5 DAY);
-- Output: NULL
MySQL’s syntax is straightforward, but always validate input to avoid NULL issues.
PostgreSQL: Using Operators and INTERVAL
PostgreSQL handles date arithmetic using standard arithmetic operators with interval values.
Add days:
SELECT DATE '2024-01-15' + INTERVAL '7 days';
-- Output: '2024-01-22 00:00:00'
Subtract months:
SELECT DATE '2024-04-18' - INTERVAL '2 months';
-- Output: '2024-02-18 00:00:00'
PostgreSQL returns a TIMESTAMP by default. If you need only the date part, consider formatting the result or casting it explicitly.
SQL Server: Using DATEADD Function
SQL Server uses DATEADD() for both adding and subtracting dates.
Add 2 months:
SELECT DATEADD(MONTH, 2, '2024-01-21');
-- Output: '2024-03-21 00:00:00'
Subtract 2 years:
SELECT DATEADD(YEAR, -2, '2024-12-28');
-- Output: '2022-12-28 00:00:00'
SQL Server doesn’t support DATE_SUB()—use negative intervals instead.
Bonus Tip: For date differences, use DATEDIFF()—a separate function.
FAQ
Is date addition universal across databases?
No. The logic is similar, but the syntax is database-specific. For example, PostgreSQL uses + INTERVAL, while MySQL and SQL Server use specific functions.
Can I pass NULL into these functions?
Yes, but the result will be NULL. It’s always a good idea to sanitize or default-check your input.
What’s the best way to practice these?
Use a SQL client like DbVisualizer to run queries interactively across different DBMS platforms.
What’s the most common mistake?
Swapping the order of parameters in SQL Server's DATEADD() or omitting the INTERVAL keyword in PostgreSQL.
Conclusion
Adding and subtracting dates in SQL is essential for handling time-based operations like scheduling, billing, and reporting. With MySQL, PostgreSQL, and SQL Server, you now know the exact syntax and behavior for each platform. Once you master these operations, your SQL queries become much more powerful—and your data workflows much more efficient.
Read Adding Dates in SQL: A Complete Tutorial for more info.
Top comments (0)