w3resource logo


MySQL SUBDATE function

MySQL SUBDATE() function

Secondary Nav

SUBDATE() function

MySQL SUBDATE() subtracts a time value (as interval) from a given date.

DATE_SUB() and SUBDATE are synonyms of SUBDATE().

Syntax :

SUBDATE(date, INTERVAL expr unit)

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.

Video Presentation

Pictorial Presentation

Pictorial Presentation of MySQL SUBDATE() function

Example: MySQL SUBDATE() function

The following statement will return a date after subtracting 10 days (notice that INTERVAL keyword is used) from the specified date 2008-05-15.

SELECT SUBDATE('2008-05-15', INTERVAL 10 DAY);

Output :

mysql> SELECT SUBDATE('2008-05-15', INTERVAL 10 DAY);
+----------------------------------------+
| SUBDATE('2008-05-15', INTERVAL 10 DAY) |
+----------------------------------------+
| 2008-05-05                             | 
+----------------------------------------+
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">
<title>example-SUBDATE-function - php mysql examples | w3resource</title>
<meta name="description" content="example-SUBDATE-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>Calculate a date after subtracting 10 days from 2008-05-15:</h2>
<table class='table table-bordered'>
<tr>
<th>Calculated output</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 SUBDATE("2008-05-15", INTERVAL 10 DAY)') as $row) {
echo "<tr>";
echo "<td>" . $row['SUBDATE("2008-05-15", INTERVAL 10 DAY)'] . "</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-subdate-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 SUBDATE('2008-05-15', INTERVAL 10 DAY)";
rs = statement.executeQuery(Data);
%>
<TABLE border="1">
<tr width="10" bgcolor="#9979">
<td>Calculated output</td>
</tr>
<%
while (rs.next()) {
%>
<TR>
<TD><%=rs.getString("SUBDATE('2008-05-15', INTERVAL 10 DAY)")%></TD>
</TR>
<%   }    %>
</table>
<%
rs.close();
statement.close();
connection.close();
} catch (Exception ex) {
out.println("Can’t connect to database.");
}
%>
</body>
</html>

Example : SUBDATE() function without INTERVAL keyword

The following statement will return a date after subtracting 10 days (notice that INTERVAL keyword is not used) from the specified date 2008-05-15.

SELECT SUBDATE('2008-05-15', 10);

Output :

mysql> SELECT SUBDATE('2008-05-15', 10);
+---------------------------+
| SUBDATE('2008-05-15', 10) |
+---------------------------+
| 2008-05-05                | 
+---------------------------+
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">
<title>example1-SUBDATE-function - php mysql examples | w3resource</title>
<meta name="description" content="example1-SUBDATE-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>Calculate a date after subtracting 10 days from 2008-05-15:</h2>
<table class='table table-bordered'>
<tr>
<th>Calculated output</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 SUBDATE("2008-05-15", 10)') as $row) {
echo "<tr>";
echo "<td>" . $row['SUBDATE("2008-05-15", 10)'] . "</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.



Is this content useful for you?

 


You might be using Adblocker. Since w3resource does not charge anything from users, all of our expenses (e.g. Hosting, Content Creation, Development etc.) are met by advertisement. Please whitelist w3resource.