MySQL Date with where
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>
Online Practice Editor:
Previous: Date with not null
Next: Select with date - dayofmonth()
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://www.w3resource.com/mysql/advance-query-in-mysql/date-with-where.php
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics