SQL get month from the date

• To remember important dates, we can store them in the database tables of SQL.
• There may arise a situation in which instead of retrieving an entire date from the column of an SQL table, you only want the month of the date to be fetched from the table’s columns.
• There are different dates functions available in SQL, using which we can fetch different parts of date from the columns as per our requirements.
• The MONTH () function in SQL is used to get the month from an entire date stored in the table’s column.
• Along with retrieving the date in the default format in which it is stored, there is a DATE_FORMAT () function in SQL using which the date values can be retrieved in a more readable format.

Let us see few practical examples to understand this concept more clearly. We will use the MySQL database for writing all the queries.

mysql> USE dbs;  

Then we will write the following query to create a table in the ‘dbs’ database:

mysql> CREATE TABLE student(ID INT PRIMARY KEY, Name VARCHAR(20), Percentage INT, Location VARCHAR(20), DateOfBirth DATE);  

In the above query, the column named ‘DateOfBirth’ will store the date since the data type of this column is set as a ‘DATE’.

Now, we will write a query to insert records in the student table.

mysql> INSERT INTO student(ID, Name, Percentage, Location, DateOfBirth) VALUES (1, "Manthan Koli", 79, "Delhi", "2003-08-20"), (2, "Dev Dixit", 75, "Pune", "1999-06-17"), (3, "Aakash Deshmukh", 87, "Mumbai", "1997-09-12"), (4, "Aaryan Jaiswal", 90, "Chennai", "2005-10-02"), (5, "Rahul Khanna", 92, "Ambala", "1996-03-04");  

We will execute the SELECT query to verify that all the records are inserted successfully in the student table.

mysql> SELECT *FROM student;  

You will get the following table as output:

ID	Name	Percentage	sLocation	DateOfBirth
1	Manthan Koli	79	Delhi	2003-08-20
2	Dev Dixit	75	Pune	1999-06-17
3	Aakash Deshmukh	87	Mumbai	1997-09-12
4	Aaryan Jaiswal	90	Chennai	2005-10-02
5	Rahul Khanna	92	Ambala	1996-03-04

The above query results show that the date stored in the column ‘DateOfBirth’ is retrieved in the default format in which it is stored, i.e., ‘YYYY-MM-DD’.

Example 1:

Write a query to retrieve only the specific part of the date, i.e., a month from the column ‘DateOfBirth’.

Query:

mysql> SELECT ID, Name, Percentage, Location, MONTH (DateOfBirth) AS MonthOfBirth FROM student;  

Month () function is used in a SELECT query and applied on the column DateOfBirth to retrieve only the month from an entire date. ‘MonthOfBirth’ is the alias given using the AS keyword to store the month from the date.

You will get the following table as output:

ID	Name	Percentage	Location	MonthOfBirth
1	Manthan Koli	79	Delhi	8
2	Dev Dixit	75	Pune	6
3	Aakash Deshmukh	87	Mumbai	9
4	Aaryan Jaiswal	90	Chennai	10
5	Rahul Khanna	92	Ambala	3

The results show that the birth month of all the students is successfully retrieved in the ‘MonthOfBirth’ column.

Example 2:

Write a query to retrieve only the specific part of the date, i.e., month, in a more readable format from the column ‘DateOfBirth’.

Query:

mysql> SELECT ID, Name, Percentage, Location, DATE_FORMAT (DateTimeOfBirth, '%M') AS MonthOfBirth FROM student;  

You will get the following table as output:

ID	Name	Percentage	Location	MonthOfBirth
1	Manthan Koli	79	Delhi	August
2	Dev Dixit	75	Pune	June
3	Aakash Deshmukh	87	Mumbai	September
4	Aaryan Jaiswal	90	Chennai	October
5	Rahul Khanna	92	Ambala	March

DATE_FORMAT () function is used in a SELECT query and applied on the column DateOfBirth to retrieve only the month in a more readable format from an entire date. ‘MonthOfBirth’ is the alias given using the AS keyword to store the month from the date.