intro
After reading this guide, adding intervals to a date in SQL in MySQL, PostgreSQL, SQL Server, and Oracle will no longer have secrets for you!
Ever wondered how to add a time interval to an existing date in SQL? That is useful in several scenarios, such as automatically setting deadlines and calculating due dates at the database level. Well, this is exactly what the "SQL add to date" topic is all about!
In this tutorial, you will see what operators and functions the most common DBMS technologies provide when it comes to adding days, months, and years to a date in SQL.
Let's dive in!
Adding an Interval to a Date in SQL
The "SQL add to date" operation refers to the process of adding a certain interval, such as days, months, or years, to a given date in SQL. This functionality is crucial in various applications, including calculating deadlines, scheduling events, or forecasting future dates.
SQL is a standardized query language, but there is no standardized function or operator to add an interval to a date. This means that each DBMS usually provides custom methods for adding time intervals to dates. To achieve the desired goal, MySQL will then rely on an approach you may not be able to take in PostgreSQL or SQL server.
Use cases where you need to add to a date cover different industries or use cases, such as:
In other terms, the "SQL add to date" operation plays a key role in database management. That is why you need to learn more about it!
SQL Add to Date Functions in MySQL, PostgreSQL, SQL Server, and Oracle
In this section, you will explore how to add intervals to dates in MySQL, PostgreSQL, SQL Server, and Oracle. Each of these database systems provides its own set of functions or operators to handle the “SQL add to date” operation effectively. In detail, you will delve into the syntax and usage of these functions/operators to add days, months, and years to an SQL date.
MySQL
For adding intervals to dates, MySQL offers the DATE_ADD() function. Call it with the following syntax:
1
DATE_ADD(date_expression, INTERVAL time_interval_value unit)
Where:
Use DATE_ADD() as in the example below:
1
-- adding a day to a date
2
SELECT DATE_ADD('2024-01-06', INTERVAL 1 DAY);
3
-- 2024-01-07
4
5
-- adding a month to a date
6
SELECT DATE_ADD('2024-01-06', INTERVAL 1 MONTH);
7
-- 2024-02-06
8
9
-- adding a year to a date
10
SELECT DATE_ADD('2024-01-06', INTERVAL 1 YEAR);
11
-- 2025-01-0
PostgreSQL
In PostgreSQL, you can use arithmetic operations directly on dates thanks to the INTERVAL keyword. The syntax is straightforward:
1
date_expression + INTERVAL 'time_interval_value unit'
Where:
Use arithmetic operations to add a day, month, and year to a date in PostgreSQL as below:
1
-- adding a day to a date
2
SELECT '2024-03-17' + INTERVAL '1 day';
3
-- 2024-03-18
4
5
-- adding a month to a date
6
SELECT '2024-03-17' + INTERVAL '1 month';
7
-- 2024-04-17
8
9
-- adding a year to a date
10
SELECT '2024-03-17' + INTERVAL '1 year';
11
-- 2025-03-17
SQL Server
SQL Server provides the DATEADD() function to perform the “SQL add to date” operation adding intervals to dates. The syntax of the function is:
1
DATEADD(date_part, time_interval_value, date_expression)
Where:
See DATEADD() in action in the following sample queries:
1
-- adding a day to a date
2
SELECT DATEADD(day, 1, '2024-06-25');
3
-- 2024-06-26 00:00:00.000
4
5
-- adding a month to a date
6
SELECT DATEADD(month, 1, '2024-06-25');
7
-- 2024-07-25 00:00:00.000
8
9
-- adding a year to a date
10
SELECT DATEADD(year, 1, '2024-06-25');
11
-- 2025-06-25 00:00:00.000
Note that the SQL Server DATEADD() function returns a DATETIME value. In other words, the results will always have the time component as well.
Oracle
In Oracle Database, you can use the + operator to add days to a date:
1
SELECT TO_DATE('2024-11-15', 'YYYY-MM-DD') + 1 FROM DUALS;
2
-- 2024-11-16
To add months, you need to use the ADD_MONTHS() function instead. Its syntax is:
1
ADD_MONTHS(date_expression, number_of_months)
Where:
Use it as in this example:
1
SELECT ADD_MONTHS(TO_DATE('2024-11-15', 'YYYY-MM-DD'), 1) FROM DUALS;
2
-- 2024-12-15
By passing multiples of 12 to ADD_MONTHS(), you can add years to a date:
1
SELECT ADD_MONTHS(TO_DATE('2024-02-06', 'YYYY-MM-DD'), 12) FROM DUALS;
2
-- 2025-02-06
Congrats! You just became an “SQL add to date” expert!
Conclusion
In this tutorial, you saw how to add a time interval to an SQL date in MySQL, PostgreSQL, SQL Server, and Oracle. You now know what specific operators and functions to use to add days, months, or years to a date.
Adding intervals to SQL dates hides some traps that only a powerful database client can avoid. Enter DbVisualizer, the database client with the best user satisfaction score on the market. This full-featured tool supports many DBMS technologies, can generate ERD-like schemas, provides advanced query optimization capabilities, and much more. Try DbVisualizer for free today!
FAQ
How to add a specific number of days to a date in SQL?
To add a specific number of days to a date in SQL, you need to use date manipulation functions/operators such as DATE_ADD() in MySQL, + INTERVAL in PostgreSQL, DATEADD() in SQL Server, and + in Oracle. Make sure to specify day (or days in some DBMS) as the time unit.
Are there any limitations to SQL add to date operations?
Yes, some limitations of SQL add to date operations involve leap years, boundary conditions (e.g., adding months to dates near month-end), and differences in implementation across database systems.
What is the difference between adding intervals to DATE and TIMESTAMP data types in SQL?
The difference lies in the granularity of the result. Adding intervals to a DATE data type operates on whole days while adding intervals to a TIMESTAMP data type considers fractions of seconds. Note that most SQL operators and functions to add intervals to dates work with both DATE and DATETIME values.
What is the maximum date allowed in SQL?
The maximum date allowed in SQL depends on the DBMS technology in use. For example, in MySQL, the maximum date is 9999-12-31. If you try to add a few days to that value with DATE_ADD(), you will get a NULL as the result.
What is the minimum date allowed in SQL?
The minimum date allowed in SQL varies from database to database. In MySQL, the minimum date is 1000-01-01.
