Converting month name to month number in SQL Server

I have a column (says Period_name ) that accepts varchar value. Inside the column, I have inputs likeJan-19, Feb-19, etc. I need an SQL instruction whenever left of Period_name comes like Jan, Feb then it should convert into corresponding month number. For example

Input

Period_name
Jan-19
Feb-19

Output

Period name 
01
02

The SQL query condition is as follows:

Case 
When Left(period_name,3) in{'Jan','Feb'} Then Month(Left(period_name,3))
END 

Thanks 🙂

Answer

Just another option is to try_convert() into a date

Note: the format() is optional

Example

Declare @YourTable Table ([Period_Name] varchar(50))  Insert Into @YourTable Values 
 ('Jan-19')
,('Feb-19')

Select * 
      ,NewVal = format(try_convert(date,'01-'+Period_Name),'MM')
 from @YourTable

Returns

Period_Name NewVal
Jan-19      01
Feb-19      02