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 Version: 5.6
Video Presentation:
Pictorial Presentation:

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>
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>
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>
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>
Online Practice Editor:
All Date and Time Functions :
Click here to see the MySQL Date and time functions.
Previous: MINUTE()
Next: MONTHNAME()
- Weekly Trends
- Java Basic Programming Exercises
- SQL Subqueries
- Adventureworks Database Exercises
- C# Sharp Basic Exercises
- SQL COUNT() with distinct
- JavaScript String Exercises
- JavaScript HTML Form Validation
- Java Collection Exercises
- SQL COUNT() function
- SQL Inner Join
- JavaScript functions Exercises
- Python Tutorial
- Python Array Exercises
- SQL Cross Join
- C# Sharp Array Exercises
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