w3resource

MySQL ADDDATE() function

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 ADDDATE() Function - 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:

Pictorial Presentation of MySQL ADDDATE() function

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()



Follow us on Facebook and Twitter for latest update.