MySQL ADDDATE() function

Last update on August 19 2022 21:51:22 (UTC/GMT +8 hours)

ADDDATE() function

MySQL ADDDATE() adds a time value with a date.

The DATE_ADD() is the synonym of ADDDATE().

Syntax:

ADDDATE(date, INTERVAL expr unit), ADDDATE(expr,days)

Arguments:

Name	Description
date	A date value.
INTERVAL	Keyword.
expr	A date or datetime expression or a number.
unit	An unit, described in the following table.

Syntax Diagram:

MySQL Version: 5.6

The following table shows the expected form of the expr argument for each unit value.

Unit Value	Expected expr Format
MICROSECOND	MICROSECONDS
SECOND	SECONDS
MINUTE	MINUTES
HOUR	HOURS
DAY	DAYS
WEEK	WEEKS
MONTH	MONTHS
QUARTER	QUARTERS
YEAR	YEARS
SECOND_MICROSECOND	'SECONDS.MICROSECONDS'
MINUTE_MICROSECOND	'MINUTES:SECONDS.MICROSECONDS'
MINUTE_SECOND	'MINUTES:SECONDS'
HOUR_MICROSECOND	'HOURS:MINUTES:SECONDS.MICROSECONDS'
HOUR_SECOND	'HOURS:MINUTES:SECONDS'
HOUR_MINUTE	'HOURS:MINUTES'
DAY_MICROSECOND	'DAYS HOURS:MINUTES:SECONDS.MICROSECONDS'
DAY_SECOND	'DAYS HOURS:MINUTES:SECONDS'
DAY_MINUTE	'DAYS HOURS:MINUTES'
DAY_HOUR	'DAYS HOURS'
YEAR_MONTH	'YEARS-MONTHS'

Video Presentation:

Pictorial Presentation:

Example: MySQL ADDDATE() function

The following statement will return a date after adding 10 days with the specified date.

Code:

SELECT ADDDATE('2008-05-15', INTERVAL 10 DAY) as required_date;

Sample Output:

mysql> SELECT ADDDATE('2008-05-15', INTERVAL 10 DAY) as required_date;
+---------------+
| required_date |
+---------------+
| 2008-05-25    | 
+---------------+
1 row in set (0.04 sec)

PHP script:

<!doctype html>
<html lang="en">
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>example-ADDDATE-function - php mysql examples | w3resource</title>
<meta name="description" content="example-ADDDATE-function - php mysql examples | w3resource">
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.5/css/bootstrap.min.css">
</head>
<body>
<div class="container">
<div class="row">
<div class="col-md-12">
<h2>Calculating a date adding an interval of ten days with 2008-05-15 using MySQL:</h2>
<table class='table table-bordered'>
<tr> <th>Required Date</th>
</tr>
<?php
$hostname="your_hostname"; 
$username="your_username"; 
$password="your_password"; 
$db = "your_dbname"; 
$dbh = new PDO("mysql:host=$hostname;dbname=$db", $username, $password);
foreach($dbh->query('SELECT ADDDATE("2008-05-15", INTERVAL 10 DAY) as required_date') as $row) {
echo "<tr>";
echo "<td>" . $row['required_date'] . "</td>";
echo "</tr>"; 
}
?>
</tbody></table>
</div>
</div>
</div>
</body>
</html>

View the example in browser

JSP script:

<%@page contentType="text/html" pageEncoding="UTF-8"%>
<%@ page import="java.sql.*" %>
<%@ page import="java.io.*" %>
<!DOCTYPE html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>example-adddate-function</title>
</head>
<body>
<%
try {
Class.forName("com.mysql.jdbc.Driver").newInstance();
String Host = "jdbc:mysql://localhost:3306/w3resour_bookinfo";
Connection connection = null;
Statement statement = null;
ResultSet rs = null;
connection = DriverManager.getConnection(Host, "root", "datasoft123");
statement = connection.createStatement();
String Data = "SELECT ADDDATE('2008-05-15', INTERVAL 10 DAY) as required_date";
rs = statement.executeQuery(Data);
%>
<TABLE border="1">
<tr width="10" bgcolor="#9979">
<td>Required Date</td>
</tr>
<%
while (rs.next()) {
%>
<TR>
<TD><%=rs.getString("required_date")%></TD>
</TR>
<%   }    %>
</table>
<%
rs.close();
statement.close();
connection.close();
} catch (Exception ex) {
out.println("Can’t connect to database.");
}
%>
</body>
</html>

Example: ADDDATE() function using INTERVAL

The following statement will return a date after adding 10 months with the specified date.

Code:

SELECT ADDDATE('2008-05-15', INTERVAL 10 MONTH) as required_date;

Sample Output:

mysql> SELECT ADDDATE('2008-05-15', INTERVAL 10 MONTH) as required_date;
+---------------+
| required_date |
+---------------+
| 2009-03-15    | 
+---------------+
1 row in set (0.00 sec)

PHP script:

<!doctype html>
<html lang="en">
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>example1-ADDDATE-function - php mysql examples | w3resource</title>
<meta name="description" content="example1-ADDDATE-function - php mysql examples | w3resource">
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.5/css/bootstrap.min.css">
</head>
<body>
<div class="container">
<div class="row">
<div class="col-md-12">
<h2>Calculating a date adding an interval of ten months with 2008-05-15 using MySQL:</h2>
<table class='table table-bordered'>
<tr>
<th>Required Date</th>
</tr>
<?php
$hostname="your_hostname";
$username="your_username";
$password="your_password";
$db = "your_dbname"; 
$dbh = new PDO("mysql:host=$hostname;dbname=$db", $username, $password);
foreach($dbh->query('SELECT ADDDATE("2008-05-15", INTERVAL 10 MONTH) as required_date') as $row) {
echo "<tr>";
echo "<td>" . $row['required_date'] . "</td>";
echo "</tr>";
}
?>
</tbody></table>
</div>
</div>
</div>
</body>
</html>

View the example in browser

Example: ADDDATE() function with interval more than one years

The following statement will return a date after adding 2 years with the specified date.

Code:

SELECT ADDDATE('2008-05-15', INTERVAL 2 YEAR) as required_date;

Sample Output:

mysql> SELECT ADDDATE('2008-05-15', INTERVAL 2 YEAR) as required_date;
+---------------+
| required_date |
+---------------+
| 2010-05-15    | 
+---------------+
1 row in set (0.00 sec)

PHP script:

<!doctype html>
<html lang="en">
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>example2-ADDDATE-function - php mysql examples | w3resource</title>
<meta name="description" content="PHP MySQL PDO Example">
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.5/css/bootstrap.min.css">
</head>
<body>
<div class="container">
<div class="row">
<div class="col-md-12">
<h2>Calculating a date adding an interval of two years with 2008-05-15 using MySQL:</h2>
<table class='table table-bordered'>
<tr>
<th>Required Date</th>
</tr>
<?php
$hostname="your_hostname"; 
$username="your_username";
$password="your_password";
$db = "your_dbname";
$dbh = new PDO("mysql:host=$hostname;dbname=$db", $username, $password);
foreach($dbh->query('SELECT ADDDATE("2008-05-15", INTERVAL 2 YEAR) as required_date') as $row) {
echo "<tr>";
echo "<td>" . $row['required_date'] . "</td>";  
echo "</tr>"; 
}
?>
</tbody></table>
</div>
</div>
</div>
</body>
</html>

View the example in browser

Example: ADDDATE() function to add date

The following statement will return a date after adding 16 days with the specified date. In the above statement only the no of days as specified will be added with the specified date.

Code:

SELECT ADDDATE('2008-05-15',16 );

Sample Output:

mysql> SELECT ADDDATE('2008-05-15',16 );
+---------------------------+
| ADDDATE('2008-05-15',16 ) |
+---------------------------+
| 2008-05-31                | 
+---------------------------+
1 row in set (0.00 sec)

PHP script:

<!doctype html>
<html lang="en">
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>example3-ADDDATE-function - php mysql examples | w3resource</title>
<meta name="description" content="example3-ADDDATE-function - php mysql examples | w3resource">
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.5/css/bootstrap.min.css">
</head>
<body>
<div class="container">
<div class="row">
<div class="col-md-12">
<h2>Calculating a date adding sixteen days with 2008-05-15 using MySQL:</h2>
<table class='table table-bordered'>
<tr>
<th>Required Date</th>
</tr>
<?php
$hostname="your_hostname";
$username="your_username";
$password="your_password";
$db = "your_dbname";
$dbh = new PDO("mysql:host=$hostname;dbname=$db", $username, $password);
foreach($dbh->query('SELECT ADDDATE("2008-05-15",16 ) as required_date') as $row) {
echo "<tr>";
echo "<td>" . $row['required_date'] . "</td>";
echo "</tr>";
}
?>
</tbody></table>
</div>
</div>
</div>
</body>
</html>

View the example in browser

All Date and Time Functions :

Click here to see the MySQL Date and time functions.

Previous: MySQL date and time functions
Next: ADDTIME()