In this article, you’ll learn how to convert a month name to the month number in SQL Server. In SQL Server, there are no built-in functions to get a month number from a month name. So, here, you can use the MONTH()and DATEPART() functions to convert a month name to its corresponding month number.
Here are some examples to convert a month name to a month number.
SELECT MONTH('February' + '1,1') AS 'Month Number';
Month Number
------------
2
(1 row(s) affected)
So in this example, basically, what I’m doing here is fabricating a “date” that includes my specified month, so that SQL Server doesn’t throw an exception when using the MONTH() function without a valid date.
The MONTH() function takes an argument that can be resolved to a time, date, smalldatetime, datetime, datetime2, or datetimeoffset value. It is often an expression, column expression, user-defined variable, or string literal.
You can also pass more “date-like” argument by doing something like in given below example and you will get the same output as the above example:
SELECT MONTH('February' + ' 01, 2000') AS 'Month Number';
Month Number
------------
2
(1 row(s) affected)
In this example, we used the DATEPART() function to get the month number from a specified month Name in SQL Server. The DATEPART() function used to return a specific part of a date and this function returns the result as an integer value.
DECLARE @Month_Name VARCHAR(20)='September';
SELECT DATEPART(MM, @Month_Name + ' 01, 2000') AS 'Month Number';
Month Number
------------
9
(1 row(s) affected)
I hope this article will help you to understand how to convert a month name to the month number in SQL Server.
Share your valuable feedback, please post your comment at the bottom of this article. Thank you!
Comments