Date_add function to calculate month day year & time by updating mysql table

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

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.

date_add(date_field, interval expr unit);

Updating one field data by adding date ( period ) with another field value

We can update one field of a table by adding some date value to another column of the table. We will use one where clause to restrict the updating to one record only.

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)

update dt_tb set dt2=date_add(dt,interval 3 month) WHERE id = 10

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

update dt_tb set dt2=date_add(now(),interval 5 year) where id = 10

Same way let us add 300 days

update dt_tb set dt2=date_add(dt,interval 300 day) where id = 10

We can add week and quarter to the date value by using date_add function. Here is the sample query.

update dt_tb set dt2=date_add(dt,interval 3 week) where id = 10
update dt_tb set dt2=date_add(dt,interval 3 quarter) where id = 10

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

select date_add(now(),interval 15 day) as return_date

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.

insert into dt_tb(dt,dt2) values(now(), date_add(now(),interval 15 day) )

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 data

By 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

SELECT dt, date_add( dt, INTERVAL 5 HOUR ) FROM dt_tb

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.

SELECT dt, date_format(date_add( dt, INTERVAL 5 HOUR ), '%T')FROM dt_tb

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

CREATE TABLE `dt_tb` (
  `id` int(2) NOT NULL auto_increment,
  `dt` datetime NOT NULL default '0000-00-00 00:00:00',
  `dt2` datetime NOT NULL default '0000-00-00 00:00:00',
  UNIQUE KEY `id` (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=12 DEFAULT CHARSET=latin1 AUTO_INCREMENT=12 ;

-- 
-- Dumping data for table `dt_tb`
-- 

INSERT INTO `dt_tb` VALUES (1, '2007-02-15 00:00:00', '2005-01-25 00:00:00');
INSERT INTO `dt_tb` VALUES (2, '2007-02-12 23:56:54', '2005-06-12 00:00:00');
INSERT INTO `dt_tb` VALUES (10, '2007-01-22 00:00:00', '2007-04-22 00:00:00');
INSERT INTO `dt_tb` VALUES (11, '2009-03-15 07:56:14', '2009-03-30 07:56:14');

SQL Date References Difference in days between two date fields Storing date in MySQL date field

Subhendu Mohapatra

Author

🎥 Join me live on YouTube

Passionate about coding and teaching, I publish practical tutorials on PHP, Python, JavaScript, SQL, and web development. My goal is to make learning simple, engaging, and project‑oriented with real examples and source code.

Subscribe to our YouTube Channel here

DATE_ADD(): to calculate date & time

Updating one field data by adding date ( period ) with another field value

Adding day month & year to current date and storing in field.

Adding time interval to a field data

table structure and data for dt_tb table

Subhendu Mohapatra