intro
Discover different approaches to adding dates in SQL through real-world examples in MySQL, PostgreSQL, and SQL Server.
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:
1
DATE_ADD/DATE_SUB(date, INTERVAL time_interval time_unit)
Where:
datespecifies the startingDATEorDATETIMEvalue.time_intervalspecifies the interval value to be added or subtracted from the starting date. For negative intervals, it needs to start with a ``.time_unitis 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():
1
SELECT DATE_ADD('2024-10-01', INTERVAL 35 DAY);
The result will be:
1
'2024-11-05'
Similarly, you can remove 21 days with DATE_SUB():
1
SELECT DATE_SUB('2024-10-01', INTERVAL 21 DAY);
That query returns:
1
'2024-09-10'
Or, equivalently:
1
SELECT DATE_ADD('2024-10-01', INTERVAL -21 DAY);
Note the use of the - sign with DATE_ADD. Again, the result will be:
1
'2024-09-10'
Keep in mind that if date is NULL, the result will be NULL as well:
1
SELECT DATE_ADD(NULL, INTERVAL 5 DAY);
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:
1
date +/- INTERVAL time_interval time_unit
Where:
dateis the starting date to which you want to add/subtract a time interval.time_intervalis the numeric value representing the amount you want to add/subtract.time_unitis the unit of time (e.g., days, months, years) related totime_interval.
For example, assume you want to add 5 days to 2024-01-15. This is how you can do it:
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:
1
'2024-01-20 00:00:00'
Similarly, you can remove 2 months from 2024-04-18 with:
1
SELECT DATE '2024-04-18' - INTERVAL '2 months';
That PostgreSQL query would return:
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:
1
DATEADD(date_part, time_interval, date)
Where:
time_unitis the part of the date to which you want to add/subtracttime_interval(e.g.,DAY,time intervalis the numeric value representing the amount you want to add.dateis 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:
1
SELECT DATEADD(MONTH, 2, '2024-01-21');
The result will be the DATETIME value below:
1
'2024-03-21 00:00:00'
Again, let’s say you want to remove 2 years from 2024-12-28. Achieve that with:
1
SELECT DATEADD(YEAR, -2, '2024-12-28');
That query produces:
1
'2022-12-28 00:00:00'
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.
