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)

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.

Runner H image

An AI Agent That Handles Life, Not Just Work

From ordering flowers to booking your dinner — let Runner H turn your ideas into actions. No prompts, no hassle. Just outcomes.

Try for Free

Top comments (0)

Feature flag article image

Create a feature flag in your IDE in 5 minutes with LaunchDarkly’s MCP server ⏰

How to create, evaluate, and modify flags from within your IDE or AI client using natural language with LaunchDarkly's new MCP server. Follow along with this tutorial for step by step instructions.

Read full post

👋 Kindness is contagious

Explore this insightful write-up embraced by the inclusive DEV Community. Tech enthusiasts of all skill levels can contribute insights and expand our shared knowledge.

Spreading a simple "thank you" uplifts creators—let them know your thoughts in the discussion below!

At DEV, collaborative learning fuels growth and forges stronger connections. If this piece resonated with you, a brief note of thanks goes a long way.

Okay