w3resource

MySQL select with date_add()

Select with date_add()

In this page, we have discussed how to use MySQL date_add() function. This is useful to add a time interval to a given date.

Example:

Sample table: publisher


Code:

SELECT pub_name,estd 
FROM publisher 
WHERE MONTH(estd) =        
MONTH(DATE_ADD(CURDATE(),INTERVAL -7 MONTH));

Explanation

The above MySQL statement returns pub_name and estd of those publishers, whose month of an establishment is equal to the month part of a date which is seven months before the current date.

Sample Output:

mysql> SELECT pub_name,estd 
    -> FROM publisher 
    -> WHERE MONTH(estd) =        
    -> MONTH(DATE_ADD(CURDATE(),INTERVAL -7 MONTH));
+-------------------------+------------+
| pub_name                | estd       |
+-------------------------+------------+
| New Harrold Publication | 1975-09-05 | 
+-------------------------+------------+
1 row in set (0.00 sec)

Note: Since CURDATE() is used, your output may vary form the output shown.

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-select-with-date-add php mysql examples | w3resource</title>
<meta name="description" content="example-select-with-date-add 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>List of publishers with their city and date of establishment, where their respective month of establishment equals
two months before the current month:</h2>
<table class='table table-bordered'>
<tr>
<th>Publisher's name</th><th>Date of establishment</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 pub_name,estd FROM publisher WHERE MONTH(estd) = MONTH(DATE_ADD(CURDATE(),INTERVAL -7 MONTH))') as $row) {
echo "<tr>"; 
echo "<td>" . $row['pub_name'] . "</td>"; 
echo "<td>" . $row['estd'] . "</td>"; 
echo "</tr>";
}
?>
</tbody></table>
</div>
</div>
</div>
</body>
</html>

View the example in browser

Note: The link above and the select statement may return zero rows. This is only because the data available in the table agsinst which the query is executed, does not have a date present with respect to the current date. Otherwise, the query is technically correct.

Online Practice Editor:


Previous: Select with date - dayofmonth()
Next: MySQL JOINS Introduction INNER JOIN



Become a Patron!

Follow us on Facebook and Twitter for latest update.

It will be nice if you may share this link in any developer community or anywhere else, from where other developers may find this content. Thanks.

https://w3resource.com/mysql/advance-query-in-mysql/select-with-date-add.php