DATE
DATETIME
MySQL
ORACLE
POSTGRESQL
SQL
SQL SERVER

SQL Add to Date Operations: A Complete Guide

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!

Tools used in the tutorial
Tool Description Link
MySQL logo MySQL
THE MYSQL DATABASE
ORACLE logo thetable ORACLE
THE ORACLE DATABASE
PostgreSQL logo POSTGRESQL
THE POSTGRESQL DATABASE
SQL Server
SQL Server is a database management system by Microsoft

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:

  • The Finance Industry: working with dates helps with calculating interest accruals, payment schedules, or maturity dates of financial instruments.
  • Project management: Setting dates helps with project deadlines, task scheduling, and timeline tracking.
  • E-commerce platforms: dates help easily manage product promotions, calculate delivery dates, and handle inventory turnover.
  • Healthcare: Dates help with scheduling medical appointments, managing medication schedules, and defining patient appointments.

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:

Copy
        
1 DATE_ADD(date_expression, INTERVAL time_interval_value unit)

Where:

  • date_expression is the SQL date to add the interval to.
  • time_interval_value is the numerical value of the interval to add to date_expession.
  • unit is the time unit of the interval (e.g., DAY, MONTH, YEAR).

Use DATE_ADD() as in the example below:

Copy
        
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:

Copy
        
1 date_expression + INTERVAL 'time_interval_value unit'

Where:

  • date_expression is the date to which the interval will be added.
  • time_interval_value is the number of the time interval.
  • unit is the unit of the interval (e.g., day, month, year).

Use arithmetic operations to add a day, month, and year to a date in PostgreSQL as below:

Copy
        
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:

Copy
        
1 DATEADD(date_part, time_interval_value, date_expression)

Where:

  • date_part is the part of the date to add the interval to (e.g., day, month, year).
  • time_interval_value is the integer value of the units to add to the date_part of the date.
  • date_expression is the date to which the interval is added.

See DATEADD() in action in the following sample queries:

Copy
        
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:

Copy
        
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:

Copy
        
1 ADD_MONTHS(date_expression, number_of_months)

Where:

  • date_expression is the date to add the months to.
  • number_of_months represents the number of months to add to the date.

Use it as in this example:

Copy
        
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:

Copy
        
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.

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 Alias: Everything You Need to Know About AS in SQL

author Antonello Zanini tags MySQL ORACLE POSTGRESQL SQL SQL SERVER 7 min 2024-07-15
title

How to Compare SQL Dates

author Antonello Zanini tags MySQL ORACLE POSTGRESQL SQL SQL SERVER 4 min 2024-07-11
title

REGEXP_MATCH SQL Function: The Complete PostgreSQL Guide

author Antonello Zanini tags POSTGRESQL REGEXP SQL 8 min 2024-07-04
title

Types of Databases Explained

author Lukas Vileikis tags MySQL 6 min 2024-07-01
title

MySQL ALTER TABLE Explained

author Lukas Vileikis tags MySQL SQL 7 min 2024-06-27
title

Error: MySQL Shutdown Unexpectedly. Causes & Solutions

author Lukas Vileikis tags MySQL SQL 4 min 2024-06-24
title

Primary Key vs. Foreign Key: A Complete Comparison

author Lukas Vileikis tags MySQL POSTGRESQL SQL SQL SERVER 7 min 2024-06-20
title

A Complete Guide to the SQL LIKE Operator

author Antonello Zanini tags MySQL ORACLE POSTGRESQL SQL SQL SERVER 8 min 2024-06-17
title

A Definitive Guide to Postgres Foreign Key

author Leslie S. Gyamfi tags POSTGRESQL SQL 12 min 2024-06-13
title

MySQL IFNULL - Everything You Need to Know

author Leslie S. Gyamfi tags IFNULL MySQL 6 min 2024-06-13

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 ↗