w3resource

MySQL select with date dayofmonth()

Select with date dayofmonth()

In this page, we have discussed how to use MySQL dayofmonth() function. This is useful to retrieve the day of a month for a given date.

Example:

Sample table: publisher


Code:

SELECT pub_name,pub_city,estd,dayofmonth(estd)
FROM publisher
WHERE dayofmonth(estd)>5;

Explanation:

The DAYOFMONTH() function is used to get the day of a month of the establishment date of the publishers, if the day of a month is more than 5. The statement also returns pub_name,pub_city and estd form publisher table.

Sample Output:

mysql> SELECT pub_name,pub_city,estd,dayofmonth(estd)
    -> FROM publisher
    -> WHERE dayofmonth(estd)>5;
+------------------------------+-----------+------------+------------------+
| pub_name                     | pub_city  | estd       | dayofmonth(estd) |
+------------------------------+-----------+------------+------------------+
| Jex Max Publication          | New York  | 1969-12-25 |               25 | 
| Ultra Press Inc.             | London    | 1948-07-10 |               10 | 
| Summer Night Publication     | New York  | 1990-12-10 |               10 | 
| Pieterson Grp. of Publishers | Cambridge | 1950-07-15 |               15 | 
+------------------------------+-----------+------------+------------------+
4 rows in set (0.01 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-select-with-dayofmonth - php mysql examples | w3resource</title>
<meta name="description" content="example-select-with-dayofmonth - 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, date of establishment and month of establishment, where the  publisher was established after 5th day of their respective month of establishment:</h2>
<table class='table table-bordered'>
<tr>
<th>Publisher's name</th><th>Publisher's city</th><th>Date of establishment</th><th>Month 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,pub_city,estd,dayofmonth(estd) FROM publisher WHERE dayofmonth(estd)>5') as $row) {
echo "<tr>";
echo "<td>" . $row['pub_name'] . "</td>";
echo "<td>" . $row['pub_city'] . "</td>";
echo "<td>" . $row['estd'] . "</td>";
echo "<td>" . $row['dayofmonth(estd)'] . "</td>";
echo "</tr>";
}
?>
</tbody></table>
</div>
</div>
</div>
</body>
</html>

View the example in browser

Online Practice Editor:


Previous: Date with where
Next: Select with date_add()



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-dayofmonth.php