In SQL Server, you can use the MONTH() function to convert a month name to its corresponding number.

Example

Here’s an example of converting a month name into the month number.

SELECT MONTH('September' + '1,1');

Result:

9

What I’m doing here is basically fabricating a “date” (that includes my specified month) so that SQL Server doesn’t throw an error when using the MONTH() function without a valid date.

This function accepts any expression that can be resolved to a time, date, smalldatetime, datetime, datetime2, or datetimeoffset value. It can be an expression, column expression, user-defined variable, or string literal.

More “Date-Like”

You could also provide a more “date-like” argument by doing something like this:

SELECT MONTH('September' + ' 01, 1900');

Result:

9

Current Day & Year

Or if you prefer to use the current day and year, you could do something like this:

SELECT MONTH('September' + FORMAT(GETDATE(), 'd,y'));

Result:

9

Just to be clear though, unless the specified month is the same as the current month, you’ll end up with a different date than the current date.

Here’s an example to illustrate what I mean:

SELECT 
  FORMAT(GETDATE(), 'MMMM d, yyyy') AS [Today],
  'September' + FORMAT(GETDATE(), ' d, yyyy') AS [Modified],
  MONTH('September' + FORMAT(GETDATE(), ' d, yyyy')) AS [Month Number];

Result:

 +----------------+--------------------+----------------+
 | Today          | Modified           | Month Number   |
 |----------------+--------------------+----------------|
 | March 22, 2020 | September 22, 2020 | 9              |
 +----------------+--------------------+----------------+ 

I ran this query on March 22, 2020, but the modified date is September 22, 2020.

Regardless, the correct month number is returned when we use MONTH().