MySQL Date with where

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

Date with where

In this page, we have discussed how to use MySQL DATE function (MONTH function in the attached example) with WHERE clause to introduce some conditions along with the expression.

Example:

Sample table: publisher

Code:

SELECT pub_name,pub_city,country,estd 
FROM publisher         
WHERE MONTH(estd)=12;

Explanation:

The above statement will filter those publishers from publisher table who was estd in the month of December.

Sample Output:

mysql> SELECT pub_name,pub_city,country,estd 
    -> FROM publisher         
    -> WHERE MONTH(estd)=12;
+--------------------------+----------+---------+------------+
| pub_name                 | pub_city | country | estd       |
+--------------------------+----------+---------+------------+
| Jex Max Publication      | New York | USA     | 1969-12-25 | 
| Summer Night Publication | New York | USA     | 1990-12-10 | 
+--------------------------+----------+---------+------------+
2 rows 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>example-date-with-where php mysql examples | w3resource</title>
<meta name="description" content="example-date-with-where 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, country and date of establishment, if month of establishment is available:</h2>
<table class='table table-bordered'>
<tr>
<th>Publisher's name</th><th>Date of establishment</th><th>Current date</th><th>AGE</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,pub_city,country,estd FROM publisher WHERE MONTH(estd) = 12') as $row) {
echo "<tr>";
echo "<td>" . $row['pub_name'] . "</td>";
echo "<td>" . $row['pub_city'] . "</td>";
echo "<td>" . $row['country'] . "</td>"; 
echo "<td>" . $row['estd'] . "</td>";
echo "</tr>";
}
?>
</tbody></table>
</div>
</div>
</div>
</body>
</html>

View the example in browser

Online Practice Editor:

Previous: Date with not null
Next: Select with date - dayofmonth()