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.
Then we will write the following query to create a table in the 'dbs' database:
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.
We will execute the SELECT query to verify that all the records are inserted successfully in the student table.
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:
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:
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.
