w3resource

MySQL MONTH() function

MONTH() function

MySQL MONTH() returns the MONTH for the date within a range of 1 to 12 ( January to December). It Returns 0 when MONTH part for the date is 0.

Syntax:

MONTH(date1)

Where date1 is a date.

Syntax Diagram:

MySQL MONTH() Function - Syntax Diagram

MySQL Version: 5.6


Video Presentation:

Pictorial Presentation:

Pictorial Presentation of MySQL MONTH() function

Example: MySQL MONTH() function

The following statement will return the MONTH for the given date 2009-05-18.

Code:

SELECT MONTH('2009-05-18');

Sample Output: 

mysql> SELECT MONTH('2009-05-18');
+---------------------+
| MONTH('2009-05-18') |
+---------------------+
|                   5 | 
+---------------------+
1 row 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-MONTH-function - php mysql examples | w3resource</title>
<meta name="description" content="example-MONTH-function - 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>Extract MONTH from 2009-05-18:</h2>
<table class='table table-bordered'>
<tr>
<th>MONTH from 2009-05-18</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 MONTH("2009-05-18")') as $row) {
echo "<tr>";  
echo "<td>" . $row['MONTH("2009-05-18")'] . "</td>"; 
echo "</tr>"; 
}
?>
</tbody></table>
</div>
</div>
</div>
</body>
</html>

View the example in browser

JSP script:

<%@page contentType="text/html" pageEncoding="UTF-8"%>
<%@ page import="java.sql.*" %>
<%@ page import="java.io.*" %>
<!DOCTYPE html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>example-month-function</title>
</head>
<body>
<%
try {
Class.forName("com.mysql.jdbc.Driver").newInstance();
String Host = "jdbc:mysql://localhost:3306/w3resour_bookinfo";
Connection connection = null;
Statement statement = null;
ResultSet rs = null;
connection = DriverManager.getConnection(Host, "root", "datasoft123");
statement = connection.createStatement();
String Data ="SELECT MONTH('2009-05-18')";
rs = statement.executeQuery(Data);
%>
<TABLE border="1">
<tr width="10" bgcolor="#9979">
<td>MONTH from 2009-05-18</td>
</tr>
<%
while (rs.next()) {
%>
<TR>
<TD><%=rs.getString("MONTH('2009-05-18')")%></TD>
</TR>
<%   }    %>
</table>
<%
rs.close();
statement.close();
connection.close();
} catch (Exception ex) {
out.println("Can’t connect to database.");
}
%>
</body>
</html>

Example : MONTH() function with current date

The above statement will return the MONTH for the current date obtained by CURRENT_DATE().

Code:

SELECT MONTH(CURRENT_DATE());

Note: Since CURRENT_DATE() is used, your output may vary from the output shown.

Sample Output:

mysql> SELECT MONTH(CURRENT_DATE());
+-----------------------+
| MONTH(CURRENT_DATE()) |
+-----------------------+
|                     4 | 
+-----------------------+
1 row 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>example1-MONTH-function - php mysql examples | w3resource</title>
<meta name="description" content="example1-MONTH-function - 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>Display MONTH for current date:</h2>
<table class='table table-bordered'>
<tr> 
<th>MONTH for current date</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 MONTH(CURRENT_DATE())') as $row) {
echo "<tr>";
echo "<td>" . $row['MONTH(CURRENT_DATE())'] . "</td>";
echo "</tr>";
}
?>
</tbody></table>
</div>
</div>
</div>
</body>
</html>

View the example in browser

Example: MONTH() function using table

Sample table: publisher

The following statement will return the MONTH as ‘Estd MONTH’ for the ‘estd’ column as 'Estd.MONTH' (along with the name of the publisher and date of establishment) from the publisher table.

Code:

SELECT pub_name,estd,MONTH(estd) as 'Estd. MONTH'
FROM publisher;

Explanation:

The above statement will return the MONTH as ‘Estd MONTH’ for the ‘estd’ column as 'Estd.MONTH' (along with the name of the publisher and date of establishment) from the publisher table.

Sample Output:

mysql> SELECT pub_name,estd,MONTH(estd) as 'Estd. MONTH'
    -> FROM publisher;
+------------------------------+------------+-------------+
| pub_name                     | estd       | Estd. MONTH |
+------------------------------+------------+-------------+
| Jex Max Publication          | 1969-12-25 |          12 | 
| BPP Publication              | 1985-10-01 |          10 | 
| New Harrold Publication      | 1975-09-05 |           9 | 
| Ultra Press Inc.             | 1948-07-10 |           7 | 
| Mountain Publication         | 1975-01-01 |           1 | 
| Summer Night Publication     | 1990-12-10 |          12 | 
| Pieterson Grp. of Publishers | 1950-07-15 |           7 | 
| Novel Publisher Ltd.         | 2000-01-01 |           1 | 
+------------------------------+------------+-------------+
8 rows in set (0.15 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>example2-MONTH-function - php mysql examples | w3resource</title>
<meta name="description" content="example2-MONTH-function - 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 date of establishment and MONTH of establishment:</h2>
<table class='table table-bordered'>
<tr>
<th>Publisher</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,estd,MONTH(estd) as "Estd. MONTH"
FROM publisher') as $row) {
echo "<tr>";
echo "<td>" . $row['pub_name'] . "</td>";
echo "<td>" . $row['estd'] . "</td>";
echo "<td>" . $row['Estd. MONTH'] . "</td>";
echo "</tr>";
}
?>
</tbody></table>
</div>
</div>
</div>
</body>
</html>

View the example in browser

Example: MONTH() function with where clause

The following statement will return the name of the publisher, date of the establishment and MONTH of the establishment as 'Estd.MONTH', making sure that the value of the 'Estd.MONTH' is more than 7.

Sample table: publisher

Code:

SELECT pub_name,estd,MONTH(estd) as 'Estd. MONTH'              
FROM publisher        
WHERE MONTH(estd)>7;

Sample Output:

mysql> SELECT pub_name,estd,MONTH(estd) as 'Estd. MONTH'              
    -> FROM publisher        
    -> WHERE MONTH(estd)>7;
+--------------------------+------------+-------------+
| pub_name                 | estd       | Estd. MONTH |
+--------------------------+------------+-------------+
| Jex Max Publication      | 1969-12-25 |          12 | 
| BPP Publication          | 1985-10-01 |          10 | 
| New Harrold Publication  | 1975-09-05 |           9 | 
| Summer Night Publication | 1990-12-10 |          12 | 
+--------------------------+------------+-------------+
4 rows in set (0.13 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>example3-MONTH-function - php mysql examples | w3resource</title>
<meta name="description" content="example3-MONTH-function - 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 date of establishment and MONTH of establishment. Publishers those who have established be after seventh MONTH of the year, i.e. July, are listed:</h2>
<table class='table table-bordered'>
<tr>
<th>Publisher</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,estd,MONTH(estd) as "Estd. MONTH" 
FROM publisher
WHERE MONTH(estd)>7') as $row) {
echo "<tr>";
echo "<td>" . $row['pub_name'] . "</td>";
echo "<td>" . $row['estd'] . "</td>";
echo "<td>" . $row['Estd. MONTH'] . "</td>";
echo "</tr>";
}
?>
</tbody></table>
</div>
</div>
</div>
</body>
</html>

View the example in browser

Online Practice Editor:


All Date and Time Functions :

Click here to see the MySQL Date and time functions.

Previous: MINUTE()
Next: MONTHNAME()



Follow us on Facebook and Twitter for latest update.

We are closing our Disqus commenting system for some maintenanace issues. You may write to us at reach[at]yahoo[dot]com or visit us at Facebook