Converting month name to month number in SQL Server

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

SELECT shortmonths
FROM sys.syslanguages
WHERE langid = 0

Returns

Jan,Feb,Mar,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dec

So another option would be to do a substring look up on that...

SELECT 1 + NULLIF(CHARINDEX(LEFT(y.period_name,3), l.shortmonths),0)/4
FROM dbo.YourTable y
CROSS JOIN sys.syslanguages l 
WHERE l.langid = 0

You could use a case expression:

case left(period_name, 3)
    when 'Jan' then 1
    when 'Feb' then 2
    when 'Mar' then 3
    ...
    when 'Dec' then 12
end month_number

But better yet, you can take advantage of SQL Server's date parsing flexibilty. Something like '01-Jan-19' should be recognized as a date, so this should work:

month(cast('01-' + period_name as date)) month_number

Demo on DB Fiddle:

select period_name, month(cast('01-' + period_name as date)) month_number
from (values ('Jan-19'), ('Feb-19')) t(period_name)

GO

period_name | month_number
:---------- | -----------:
Jan-19      |            1
Feb-19      |            2

There are several methods available one of that is as shown below. The logic is to convert the Period_Name into a date and use the SQL Server DATEPART() Function.

You can use the TRY_CAST (Transact-SQL) function to handle the error when the part_name value is not in the expected format. It will return null in that case.

SELECT DATEPART(MM,  + '01-' + 'Jan-19') as MonthNumber

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

SELECT DATEPART(MM,  + Try_cast('01-' + Period_Name as Date)) as MonthNumber
FROM @YourTable

Here is the live db<>fiddle demo.

To get the number in double-digit you can try the following query. Note, in this case, you need to convert the number as a string.

SELECT 
    RIGHT('0' + CAST(DATEPART(MM,  + '01-' + Period_Name)  AS VARCHAR(2)), 2) as MonthNumber
FROM @YourTable

You can use the functions convert() and format() like this:

select format(convert(date, replace(Period_name, '-', ' 01 '), 1), 'MM-yy') result
from tablename

See the demo
Results:

> | result |
> | :----- |
> | 01-19  |
> | 02-19  |

You can use datepart() :

SELECT RIGHT(CONCAT('00', DATEPART(MONTH, CONVERT(DATE, CONCAT('01-', period_name)))), 2);

If you don't want 0s then only datepart() would find :

SELECT DATEPART(MONTH, CONVERT(DATE, CONCAT('01-', period_name)));

Try this:

SELECT LEFT(CONVERT(VARCHAR,CAST('01-'+Column_Name AS DATE),1),2) ,*
FROM Table_Name

This Code Works for your Case. 1st we Should convert our varchar Column into date data type by concating Default date as 01 and then we should use convert function to return for your data type.

A simply approach:

select right(t.period_name, 2) year, m.period, m.name
from
(select '01' period, 'Jan' name
  union
  select '02', 'Feb'
   ...
  union
  select '12', 'Dec') m
inner join table1 t
   on Left(t.period_name,3) = m.name
order by right(t.period_name, 2), m.period

Return:

year -- period -- name
19 ----- 01 -------- Jan
19 ----- 02 -------- Feb
19 ----- 03 -------- Mar
 ...
19 ----- 12 -------- Dec
20 ----- 01 -------- Jan
20 ----- 02 -------- Feb
20 ----- 03 -------- Mar
 ...
20 ----- 12 -------- Dec
 ...

PARSE/TRY_PARSE:

select *, 
month(try_parse(v.val as date)), 
month(try_parse(concat(left(v.val+'-', charindex('-', v.val+'-')), '01') as date))
from 
(
values('Jan-19'),('Feb-20'),('Mar-21'),('Apr-03'),('May-18'),('Jun-99'),('Jul-85'),
('Aug-15'), ('Sep-16'),('Oct-'),('Nov'), ('Dec-11')
) as v(val);