DEV Community

Cover image for How to Add and Subtract Dates in SQL (MySQL, PostgreSQL, SQL Server)
DbVisualizer
DbVisualizer

Posted on

How to Add and Subtract Dates in SQL (MySQL, PostgreSQL, SQL Server)

#date #datetime #mysql #postgressql

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'
Enter fullscreen mode Exit fullscreen mode

Subtract days using DATE_SUB:

SELECT DATE_SUB('2024-10-01', INTERVAL 15 DAY); -- Output: '2024-09-16'
Enter fullscreen mode Exit fullscreen mode

Or use DATE_ADD with a negative interval:

SELECT DATE_ADD('2024-10-01', INTERVAL -15 DAY);
Enter fullscreen mode Exit fullscreen mode

If NULL is passed as the date input:

SELECT DATE_ADD(NULL, INTERVAL 5 DAY); -- Output: NULL
Enter fullscreen mode Exit fullscreen mode

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'
Enter fullscreen mode Exit fullscreen mode

Subtract months:

SELECT DATE '2024-04-18' - INTERVAL '2 months'; -- Output: '2024-02-18 00:00:00'
Enter fullscreen mode Exit fullscreen mode

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'
Enter fullscreen mode Exit fullscreen mode

Subtract 2 years:

SELECT DATEADD(YEAR, -2, '2024-12-28'); -- Output: '2022-12-28 00:00:00'
Enter fullscreen mode Exit fullscreen mode

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)