DATE
DATETIME
MySQL
POSTGRESQL
SQL SERVER

Adding Dates in SQL: A Complete Tutorial

intro

Discover different approaches to adding dates in SQL through real-world examples in MySQL, PostgreSQL, and SQL Server.

Tools used in the tutorial
Tool Description Link
Dbvisualizer DBVISUALIZER
TOP RATED DATABASE MANAGEMENT TOOL AND SQL CLIENT
MySQL logo MySQL
THE MYSQL DATABASE
PostgreSQL logo POSTGRESQL
THE POSTGRESQL DATABASE
SQL Server
SQL Server is a database management system by Microsoft

Adding dates in SQL involves performing date arithmetic between dates and time intervals. That relies on popular date manipulation operations commonly required for various data management operations.

In this tutorial, you will gain insights into leveraging SQL functions and operators to add and subtract dates effectively in MySQL, PostgreSQL, and SQL Server.

Let's dive in!

What Is Meant By Adding Dates in SQL?

Adding dates in SQL refers to adding/subtracting time intervals to existing dates to obtain the desired dates. As you can imagine, those operations are commonly used in SQL databases to manipulate and analyze date-related data.

Examples of use cases for adding SQL dates are:

  • Transaction management: Calculating the due dates for payments by adding a fixed duration (e.g., 30 days) to a transaction date.
  • Project scheduling: Determining project deadlines by adding estimated durations to start dates.
  • Logistics and shipping: Calculating delivery dates by adding transit times to order processing dates.
  • Employee management: Computing end dates for employee contracts by adding contract durations to start dates.
  • Billing and invoicing: Adding billing cycles (e.g., monthly, quarterly) to subscription start dates to generate recurring invoices.

Each SQL database provides specific functions and operators for working with date and time types. Unfortunately, most of these operators and functions are DBMS-specific since they are not part of the standard SQL specifications.

So, let’s dig into what MySQL, PostgreSQL, and SQL Server have to offer when it comes to adding dates in SQL!

Performing Date Arithmetic in SQL

Database management systems provide specific functions or operators to calculate the arithmetic sum of a date and a time interval.

The example queries below will be run in DbVisualizer, the SQL database client with the highest user satisfaction on the market.

Time to explore different approaches to adding dates in SQL!

MySQL

MySQL exposes two main functions for performing date arithmetic:

Both share the same syntax:

Copy
        
1 DATE_ADD/DATE_SUB(date, INTERVAL time_interval time_unit)

Where:

  • date specifies the starting DATE or DATETIME value.
  • time_interval specifies the interval value to be added or subtracted from the starting date. For negative intervals, it needs to start with a ``.
  • time_unit is a keyword indicating the units in which the expression should be interpreted. For more information, see the temporal intervals page on the MySQL documentation.

Suppose you want to add 35 days to the 2024-10-01 date. This is how you can do it with DATE_ADD():

Copy
        
1 SELECT DATE_ADD('2024-10-01', INTERVAL 35 DAY);

The result will be:

Copy
        
1 '2024-11-05'

Similarly, you can remove 21 days with DATE_SUB():

Copy
        
1 SELECT DATE_SUB('2024-10-01', INTERVAL 21 DAY);

That query returns:

Copy
        
1 '2024-09-10'

Or, equivalently:

Copy
        
1 SELECT DATE_ADD('2024-10-01', INTERVAL -21 DAY);

Note the use of the - sign with DATE_ADD. Again, the result will be:

Copy
        
1 '2024-09-10'

Keep in mind that if date is NULL, the result will be NULL as well:

Copy
        
1 SELECT DATE_ADD(NULL, INTERVAL 5 DAY);
Note the “NULL” result
Note the u201cNULLu201d result

Instead, the INTERVAL section cannot be NULL. In that case, the query will fail an SQL syntax error.

Great! You just become an expert in adding dates in MySQL!

PostgreSQL

For adding dates in SQL queries in a PostgreSQL database, you cannot use the DATE_ADD function. Conversely, you have to add/subtract dates with intervals using the + and - operators, respectively.

This is the syntax of how to use those PostgreSQL operators:

Copy
        
1 date +/- INTERVAL time_interval time_unit

Where:

  • date is the starting date to which you want to add/subtract a time interval.
  • time_interval is the numeric value representing the amount you want to add/subtract.
  • time_unit is the unit of time (e.g., days, months, years) related to time_interval.

For example, assume you want to add 5 days to 2024-01-15. This is how you can do it:

Copy
        
1 SELECT DATE '2024-01-15' + INTERVAL '5 days';

Note the DATE keyword before '2024-01-15' to specify that string as a PostgreSQL date type.

The result will be the following DATETIME value:

Copy
        
1 '2024-01-20 00:00:00'
Note the DATETIME result
Note the DATETIME result

Similarly, you can remove 2 months from 2024-04-18 with:

Copy
        
1 SELECT DATE '2024-04-18' - INTERVAL '2 months';

That PostgreSQL query would return:

Copy
        
1 '2024-02-18 00:00:00'

To get the result in the right format, check our guide on date formatting in Postgres.

Well done! You now know how to perform date arithmetic in PostgreSQL.

SQL Server

To add/subtract dates with time intervals in SQL Server, you should use the DATEADD() function. This is the syntax to employ it:

Copy
        
1 DATEADD(date_part, time_interval, date)

Where:

  • time_unit is the part of the date to which you want to add/subtract time_interval (e.g., DAY,
  • time interval is the numeric value representing the amount you want to add.
  • date is the starting date you want to add or subtract time to.

Compared to MySQL, SQL Server does not provide a DATE_SUB() function. To achieve the same result, you have to specify a negative number in time_interval. For performing the difference between two dates, you need to use the DATE_DIFF() SQL Server function instead. Learn more in our guide on the DATE_DIFF SQL Server function.

For example, assume you want to add 2 months to 2024-01-21. You can do that with this SQL Server query:

Copy
        
1 SELECT DATEADD(MONTH, 2, '2024-01-21');

The result will be the DATETIME value below:

Copy
        
1 '2024-03-21 00:00:00'

Again, let’s say you want to remove 2 years from 2024-12-28. Achieve that with:

Copy
        
1 SELECT DATEADD(YEAR, -2, '2024-12-28');

That query produces:

Copy
        
1 '2022-12-28 00:00:00'
Again, note the DATETIME result
Again, note the DATETIME result

Et voilà! You just learn different approaches to adding dates in SQL Server!

Conclusion

In this guide, you have understood what adding dates in SQL means and how to do it in MySQL, PostgreSQL, and SQL Server. You now know that this operation involves adding or subtracting time intervals to the starting dates. Thanks to the use cases shown here, you have learned how to perform it in three DBMS systems through real-world scenarios.

Dealing with SQL Dates is a complex task that requires a powerful database client. Which is the best based on user satisfaction? DbVisualizer! This full-featured database client supports many DBMS technologies, can generate ERD-like schemas, and provides advanced query optimization capabilities. Try DbVisualizer for free today!

FAQ

Is adding dates in SQL possible?

Yes, adding dates in SQL is possible. DBMS technologies provide functions/operators to perform operations like adding fixed durations or subtracting dates. Those operations are crucial for various tasks such as scheduling events, calculating due dates, and forecasting trends.

Does the MySQL DATE_ADD function and SQL Server DATEADD function work the same way?

The MySQL DATE_ADD() function and SQL Server DATEADD() function operate similarly by adding intervals to dates. At the same time, they have different syntax and parameter orders. MySQL's function accepts a date in any format first, while SQL Server's function requires you to specify what you’re working with (e.g. a date) first, and provide a date afterwards.

What is the equivalent of the MySQL DATE_ADD and DATE_SUB functions in PostgreSQL?

PostgreSQL does not support DATE_ADD() or DATE_SUB(). However, it offers the + operator for adding intervals to dates and the - operator for subtracting intervals from dates. Thus, the DATE_ADD() PostgreSQL equivalent is the + operator and the PostgreSQL DATE_SUB() equivalent is the - operator.

How do I calculate the difference between two dates in SQL?

There is no standardized way to calculate the difference between two dates in SQL. Nevertheless, in MySQL and SQL Server, you can obtain the result of the subtraction of two dates using the DATEDIFF() function.

Is it possible to use arithmetic operators on dates in SQL?

Not all DBMS technologies support that, but PostgreSQL allows you to use arithmetic operators like addition (+) and subtraction (-) on dates in SQL. These operators can be used for performing date arithmetic, such as adding/subtracting intervals from dates.

Dbvis download link img
About the author
Antonello Zanini

Antonello is a software engineer, and often refers to himself as a technology bishop. His mission is to spread knowledge through writing.

The Table Icon
Sign up to receive The Table's roundup
More from the table
Title Author Tags Length Published
title

SQL TRIM: Removing Extra Space Characters From a String

author Antonello Zanini tags MySQL ORACLE POSTGRESQL SQL SQL SERVER 6 min 2024-05-16
title

PostgreSQL Full Text Search: The Definitive Guide

author Antonello Zanini tags POSTGRESQL Search 11 min 2024-05-13
title

SQL UNION ALL: Keeping Duplicates When Combining Result Sets

author Antonello Zanini tags MySQL ORACLE POSTGRESQL SQL UNION 8 min 2024-05-09
title

SQL UNION Operator: How To Combine Result Sets

author Antonello Zanini tags MySQL ORACLE POSTGRESQL SQL SQL SERVER UNION 9 min 2024-05-06
title

SQL CONVERT: The Handbook of Data Conversion in SQL

author Antonello Zanini tags CONVERT Data Conversion MySQL SQL SERVER 7 min 2024-05-02
title

SQL CASE Statement: Definitive Guide

author Antonello Zanini tags CASE ORACLE POSTGRESQL SQL SQL SERVER 5 min 2024-04-29
title

SQL ORDER BY Clause: Definitive Guide

author Antonello Zanini tags MySQL ORACLE POSTGRESQL SQL SQL SERVER 7 min 2024-04-22
title

Glossary of the SQL Commands You Need to Know

author Antonello Zanini tags MySQL ORACLE POSTGRESQL SQL SQL SERVER 12 min 2024-04-11
title

SUBSTRING_INDEX in SQL Explained: A Guide

author Lukas Vileikis tags MySQL SQL 8 min 2024-04-08
title

SQL NOT IN: the Good, Bad & the Ugly

author Lukas Vileikis tags MySQL SQL 7 min 2024-04-04

The content provided on dbvis.com/thetable, including but not limited to code and examples, is intended for educational and informational purposes only. We do not make any warranties or representations of any kind. Read more here.

Cookie policy

We use cookies to ensure that we give you the best experience on our website. However you can change your cookie settings at any time in your browser settings. Please find our cookie policy here ↗