How to Extract Month from Date in SQL?
FREE
In this article, we'll learn how to extract the month from a date in SQL. We will discuss three different ways to do this. We will use: MONTH(), DATEPART(), and FORMAT() to extract the month part of a date in SQL.
Let's look into the three different ways in which we can extract a month from a date:
1. MONTH()
Using the MONTH() function is the most straightforward way to extract the month part of a date in SQL. The MONTH() function takes one argument date and returns the month part of the specified date.
Syntax:
This function will give output in the form of an integer corresponding to the month.
2. DATEPART()
The DATEPART() function takes two arguments, the first argument is the part of the date that you want to extract(month or day, or year), and the second one is the date from which you want to extract that part.
Syntax:
Note: We can also use mm or m in place of the month as the first argument.
Transform Your Career
Choose from our industry-leading programs designed for career success
Modern Software and AI Engineering Program
Master full-stack development with AI integration
+1000 moreModern Data Science and ML with specialisation in AI
Advanced data science techniques with AI specialization
+1000 moreAdvanced AIML with Specialisation in Agentic AI
Deep dive into AIML with focus on Agentic systems
+1000 moreDevOps, Cloud & AI Platform Engineering
Build and manage AI-powered cloud infrastructure
+1000 moreAI Engineering Advanced Certification by IIT-Roorkee
Premier AI engineering certification from IIT-Roorkee
3. FORMAT()
The FORMAT() function formats a value the way you want. It takes three arguments, the first and second ones are mandatory. The first argument is the value we want to format, and the second argument is the pattern in which we want to format the value.
Syntax:
Examples to Extract Month from Date in SQL
Example 1: Using MONTH()
Output:
In the above example, we are using a straightforward method. We are directly passing the date in the MONTH() function and storing it in the column as Month using AS and getting the output for the month in number.
Turn Learning into Career Growth
Example 2: Using a variable in MONTH()
Output:
In this example, we took a different approach. Instead of directly specifying the date in the function, we use a variable and put the date in it.
Example 3: Using DATEPART()
Output:
Here, we are using the DATEPART() function. As discussed above, this function takes two arguments. Here also, we can either pass the date directly or we can use a variable as we have done in the above example.
Example 4: Using FORMAT()
The FORMAT() function is used to format values according to the specified format declared in the argument of the function. It takes three arguments, two of which are mandatory. The first argument takes the value to be formatted and the second argument is the format pattern.
Output:
Conclusion
In this article, we learned how to extract a month from a date in SQL. We saw three different methods:
- MONTH()
- DATEPART()
- FORMAT()
Each of these functions has its features and we use them accordingly.
Extracting a piece of data from a collection of information is used often. We should know different methods through which we can achieve it. In this article, we learned an important skill of retrieving monthly data from a specific date.
Learn more
Google
Amazon
Microsoft
Flipkart
Adobe1200+ more