Date_add function to calculate date & timedate_add function is used to do various calculations on date & times field of MySQL table. By using current date function we can calculate future date or time by adding day, month, year , hour, seconds etc. The calculated date value can be stored in a record. We can selectively update any date and time field by using SQL where clause in our query. We can apply this date_add function for time calculation also. The output of the date_add function we can format to display the output in the way we want.
Here is the basic syntax of the date_add function.
Updating one field data by adding date ( period ) with another field valueWe can update one field of a table by adding some date value to another field ( or column ) of the table. We will use one where clause to restrict the updating to one record only, however you can use as per your requirement and exclude the where clause.
Let us start by adding 3 month to our dt field and updating the same to dt2 field ( Download the structure and data for dt_tb table at the end)
When we run this query the value of dt2 field for the record with id =10 will become 2007-04-22. This value has come from the value of dt field ( 2007-01-22) by adding 3 months. Same way let us try by adding 5 years to the dt field and updating the dt2 field
Same way let us add 300 days
We can add week and quarter to the date value by using date_add function. Here is the sample query.
We have used one update query as an example but this can be any other query type.
Adding day month & year to current date and storing in field.Some time we will be adding dates to the current date & time and storing them in a field. Best example is the book return date of a library. From the issue date ( today's date ) we can add 15 days and use that as return date. Here is an example
The above query will display date & time by adding 15 days to current date and time.
We can use one insert command to add a record by storing present date & time in one field and return date ( by adding 15 days ) in another field.
The above query will add one record with issue date or current date and another field with return date by adding 15 days to current date.
We can use similar query for fixed deposit maturity date by adding interval to current date in a bank application.
Adding time interval to a field dataBy using date_add function we are not only adding day, month, year etc we can also add hour, minutes, seconds to the record. Here is an example of adding hour
Above query will display dt field values in one column and one more column by adding 5 hours to the time value of dt field. We can add various date formats to above query to display data based on our requirement. We will modify the above code to display only time part of the date data by using date_format function.
The above query can be modified to display other types of date formats.
We have used different types queries with its combinations in a date_add function to explore different requirements.
table structure and data for dt_tb table
This article is written by plus2net.com team.
▼ More on Date & Time functions in SQL