MySQL MariaDB

What is DATE_ADD() function in MySQL

There are many built-in functions that are supported by MySQL to handle data using the SQL query language. The readers who don’t know much about functions; functions take different parameters and return the single output. The DATE_ADD() is also a built-in function used in MySQL. For what purpose DATE_ADD() function is used and how is it employed? All this is explained in this article.

What is DATE_ADD() function in MySQL()

In MySQL, there are many sub-functions that belong to the Date function, DATE_ADD() is one of them, and is used to modify the date. If we want to add days, years, or months in the date we can do so by using the function of DATE_ADD(). The general syntax of using the DATE_ADD():

The explanation of the above syntax is:

  • Use the function DATE_ADD() to modify the date or time
  • Replace the “date” with the date on which you want to make modifications
  • Use the clause of “INTERVAL” which tells the MySQL to add the interval in the provided date according to the given instructions
  • Replace the clause “value” with the exact value you want to make changes; it can be negative as well as positive
  • Replace the clause “addunit” in which you want to make changes like MONTH, DAY, YEAR

To understand it more clearly, we will consider some examples, we will take a date and make some changes in its month by using a command:

SELECT DATE_ADD("2021-11-21", INTERVAL -4 MONTH);

In the above output, we can see that the month has been changed to July instead of November, similarly, we can also change the year by using the DATE_ADD():

SELECT DATE_ADD("2021-11-17", INTERVAL +10 YEAR);

The ten years have been advanced predicted using the DATE_ADD(), likewise, if we want to modify the day we can do so using it:

SELECT DATE_ADD(1995-08-10, INTERVAL 10 DAY);

It is to be noted here if no sign is used with the values by default MySQL will consider it with a positive sign and show the results according to the positive sign. The DATE_ADD() function is also applicable on parameters of time, for example, we can modify the minutes using the command:

SELECT DATE_ADD(2021-11-15 16:34:44, INTERVAL 05 MINUTE);

In the same way, using the below-mentioned command time will be set to 12:00:00:

SELECT DATE_ADD('2021-01-01', INTERVAL 12 HOUR);

Also, to add a quarter (adding four consecutive time intervals), run the command:

SELECT DATE_ADD('2021-02-13', INTERVAL 1 QUARTER);

Conclusion

MySQL is the most popular relational database management system that is used by many website servers to manage their website data. In managing the data of websites, MySQL uses functions that provide great help to users in saving their time. In this post, we have discussed the DATE_ADD() function that belongs to the DATE() built-in function and is used to modify the dates as well as time. We also modified different dates and times using the DATE_ADD() function so that the readers can understand it properly.

About the author

Hammad Zahid

I'm an Engineering graduate and my passion for IT has brought me to Linux. Now here I'm learning and sharing my knowledge with the world.