MYSQL Tutorial

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.


Sample table: publisher


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


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">
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<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">
<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'>
<th>Publisher's name</th><th>Publisher's city</th><th>Date of establishment</th><th>Month of establishment</th>
$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>";

View the example in browser

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

New Content: Composer: Dependency manager for PHP, R Programming