convert month name to number sql

Use Combine DATEPART() and CAST() SQL Server Function.

For example:
SELECT DATEPART(mm,CAST('February'+ ' 1900' AS DATETIME))NumOfThisMonth

#Results:
NumOfThisMonth
2

select OrdNum,DATEPART(mm,CAST(MonthNames+ ' 1900' AS DATETIME))NumOfMonth from (
    select (1)OrdNum,'January'MonthNames union
    select (2),'February' union
    select (3),'March' union
    select (4),'April' union
    select (5),'May' union
    select (6),'June' union
    select (7),'July' union
    select (8),'August' union
    select (9),'September' union
    select (10),'October' union
    select (11),'November' union
    select (12),'December'
)v0

Compare with this another way:
SELECT CHARINDEX('OCT','XXJANFEBMARAPRMAYJUNJULAUGSEPOCTNOVDEC')/3 AS monthNumber

#Results:
monthNumber
10

select OrdNum,DATEPART(mm,CAST(MonthNames+ ' 1900' AS DATETIME))NumOfMonth,
CHARINDEX(MonthNames,'XXJANFEBMARAPRMAYJUNJULAUGSEPOCTNOVDEC')/3 AS monthNumber
from (
    select (1)OrdNum,'JAN'MonthNames union
    select (2),'FEB' union
    select (3),'MAR' union
    select (4),'APR' union
    select (5),'MAY' union
    select (6),'JUN' union
    select (7),'JUL' union
    select (8),'AUG' union
    select (9),'SEP' union
    select (10),'OCT' union
    select (11),'NOV' union
    select (12),'DEC'
)v1

OrdNum    NumOfMonth    monthNumber
1         1             1
2         2             2
3         3             3
4         4             4
5         5             5
6         6             6
7         7             7
8         8             8
9         9             9
10        10            10
11        11            11
12        12            12

sql datepart

DATEPART() SQL Server function Statement:

DATEPART(datepart,date)

declare @ThisDate datetime set @ThisDate='2012-08-30 08:45:08.717'  
select ('yy')DATEPARTInit,('year')UseForShow,DATEPART(yy,@ThisDate)ShowData Union
select ('qq'),('quarter'),DATEPART(qq,@ThisDate) Union
select ('mm'),('month'),DATEPART(mm,@ThisDate) Union
select ('dy'),('dayofyear'),DATEPART(dy,@ThisDate) Union
select ('dd'),('day'),DATEPART(dd,@ThisDate) Union
select ('wk'),('week'),DATEPART(wk,@ThisDate) Union
select ('dw'),('weekday'),DATEPART(dw,@ThisDate) Union
select ('hh'),('hour'),DATEPART(hh,@ThisDate) Union
select ('mi'),('minute'),DATEPART(mi,@ThisDate) Union
select ('ss'),('second'),DATEPART(ss,@ThisDate) Union
select ('ms'),('second'),DATEPART(ms,@ThisDate) --union
--select ('mcs'),('microsecond'),DATEPART(mcs,@ThisDate) union
--select ('ns'),('nanosecond'),DATEPART(ns,@ThisDate)

#Results:
DatePartInit    UseForShow ShowData
dd              day        30
dw              weekday    5
dy              dayofyear  243
hh              hour       8
mi              minute     45
mm              month      8
ms              second     717
qq              quarter    3
ss              second     8
wk              week       35
yy              year       2012