Get the month numbe, skipping only the name of the month

Question

I want to get no month when I know only the name of the month. (If month name January should return 1)

I have tried this.

SELECT DATEPART(MM,'january 01 2011')

But here I want to convey the meaning of the entire date (January 01 2011). But I only need to pass the name of the month. (Without using the "Case")

Is there any way to do this?

Answer 1

You can pass the name of the month and add 01 2011(or any other day + year you want), for example:

@declare monthName varchar(20);
@set monthName = 'january';

SELECT DATEPART(MM,monthName+' 01 2011')

or

SELECT MONTH(monthName+' 01 2011')

Answer 2

you can also use Case

DECLARE @month VARCHAR(15)='mar'

SELECT CASE @month
         WHEN 'Jan' THEN 1
         WHEN 'feb' THEN 2
         WHEN 'mar' THEN 3
         WHEN 'apr' THEN 4
         WHEN 'may' THEN 5
         WHEN 'jun' THEN 6
         WHEN 'jul' THEN 7
         WHEN 'aug' THEN 8
         WHEN 'sep' THEN 9
         WHEN 'oct' THEN 10
         WHEN 'nov' THEN 11
         WHEN 'dec' THEN 12
       END 

Answer 3

@declare monthName varchar(20);
@set monthName = 'JUNE';

SELECT DATEPART(MM,monthName+' 01 2014') //Monthname +'any date and year'