Function & SSIS Expression to Retrieve MonthNumber in YYYYMM Format

TSQL Function
E.g. 1. Following function can be used to return Month number in YYYYMM format for a month name in "MON-YYYY", "MON YYYY", "MON/YYYY" Formats.

CREATE FUNCTION dbo.[Retrieve MonthNumber(YYYYMM)](@MonthName Varchar(50), @MonthNameFormat Varchar(50))
RETURNS INT AS
BEGIN
    DECLARE @MonthNumber INT;
    SET @MonthName = UPPER(@MonthName);
    RETURN CASE
        WHEN UPPER(@MonthNameFormat) = 'MON-YYYY' OR UPPER(@MonthNameFormat) = 'MON YYYY' THEN
            CASE
                WHEN LEFT(@MonthName, 3) = 'JAN' THEN  CAST(RIGHT(@MonthName,4) + '01' AS INTEGER)
                WHEN LEFT(@MonthName, 3) = 'FEB' THEN  CAST(RIGHT(@MonthName,4) + '02' AS INTEGER)
                WHEN LEFT(@MonthName, 3) = 'MAR' THEN  CAST(RIGHT(@MonthName,4) + '03' AS INTEGER)
                WHEN LEFT(@MonthName, 3) = 'APR' THEN  CAST(RIGHT(@MonthName,4) + '04' AS INTEGER)
                WHEN LEFT(@MonthName, 3) = 'MAY' THEN  CAST(RIGHT(@MonthName,4) + '05' AS INTEGER)
                WHEN LEFT(@MonthName, 3) = 'JUN' THEN  CAST(RIGHT(@MonthName,4) + '06' AS INTEGER)
                WHEN LEFT(@MonthName, 3) = 'JUL' THEN  CAST(RIGHT(@MonthName,4) + '07' AS INTEGER)
                WHEN LEFT(@MonthName, 3) = 'AUG' THEN  CAST(RIGHT(@MonthName,4) + '08' AS INTEGER)
                WHEN LEFT(@MonthName, 3) = 'SEP' THEN  CAST(RIGHT(@MonthName,4) + '09' AS INTEGER)
                WHEN LEFT(@MonthName, 3) = 'OCT' THEN  CAST(RIGHT(@MonthName,4) + '10' AS INTEGER)
                WHEN LEFT(@MonthName, 3) = 'NOV' THEN  CAST(RIGHT(@MonthName,4) + '11' AS INTEGER)
                WHEN LEFT(@MonthName, 3) = 'DEC' THEN  CAST(RIGHT(@MonthName,4) + '12' AS INTEGER)
                ELSE
                    0
            END
        WHEN UPPER(@MonthNameFormat) = 'MM/YYYY' OR UPPER(@MonthNameFormat) = 'MM-YYYY' THEN
            CASE
                WHEN LEFT(@MonthName, 2) = '01' THEN  CAST(RIGHT(@MonthName,4) + '01' AS INTEGER)
                WHEN LEFT(@MonthName, 2) = '02' THEN  CAST(RIGHT(@MonthName,4) + '02' AS INTEGER)
                WHEN LEFT(@MonthName, 2) = '03' THEN  CAST(RIGHT(@MonthName,4) + '03' AS INTEGER)
                WHEN LEFT(@MonthName, 2) = '04' THEN  CAST(RIGHT(@MonthName,4) + '04' AS INTEGER)
                WHEN LEFT(@MonthName, 2) = '05' THEN  CAST(RIGHT(@MonthName,4) + '05' AS INTEGER)
                WHEN LEFT(@MonthName, 2) = '06' THEN  CAST(RIGHT(@MonthName,4) + '06' AS INTEGER)
                WHEN LEFT(@MonthName, 2) = '07' THEN  CAST(RIGHT(@MonthName,4) + '07' AS INTEGER)
                WHEN LEFT(@MonthName, 2) = '08' THEN  CAST(RIGHT(@MonthName,4) + '08' AS INTEGER)
                WHEN LEFT(@MonthName, 2) = '09' THEN  CAST(RIGHT(@MonthName,4) + '09' AS INTEGER)
                WHEN LEFT(@MonthName, 2) = '10' THEN  CAST(RIGHT(@MonthName,4) + '10' AS INTEGER)
                WHEN LEFT(@MonthName, 2) = '11' THEN  CAST(RIGHT(@MonthName,4) + '11' AS INTEGER)
                WHEN LEFT(@MonthName, 2) = '12' THEN  CAST(RIGHT(@MonthName,4) + '12' AS INTEGER)
                ELSE
                    0
            END
    ELSE
        0
    END
END;
GO


Following qeury can be used to test / execute above function.
SELECT dbo.[Retrieve MonthNumber(YYYYMM)]('01/2015', 'MM/YYYY')

E.g. 2. Following function can be used to return Month number in YYYYMM format for a month name in "### Mon  YYYY" format.

CREATE FUNCTION [dbo].[GetMonthID] (
@Period VARCHAR(50))
RETURNS INT
AS
BEGIN
DECLARE @Year VARCHAR(50) = 0;
DECLARE @Month VARCHAR(50)= 0;
DECLARE @MonthID INT = NULL;
  
SELECT @Year = SUBSTRING( @Period, CHARINDEX('20',@Period, 1) , 4)
SELECT @Month = CASE 
WHEN @Period LIKE '%JAN%' THEN '01' 
WHEN @Period LIKE '%FEB%' THEN '02' 
WHEN @Period LIKE '%MAR%' THEN '03' 
WHEN @Period LIKE '%APR%' THEN '04' 
WHEN @Period LIKE '%MAY%' THEN '05' 
WHEN @Period LIKE '%JUN%' THEN '06' 
WHEN @Period LIKE '%JUL%' THEN '07' 
WHEN @Period LIKE '%AUG%' THEN '08' 
WHEN @Period LIKE '%SEP%' THEN '09' 
WHEN @Period LIKE '%OCT%' THEN '10' 
WHEN @Period LIKE '%NOV%' THEN '11' 
WHEN @Period LIKE '%DEC%' THEN '12' 
END
IF ISNUMERIC(@Year + @Month) = 1
SET @MonthID = CAST(@Year + @Month AS INT);
ELSE 
SET @MonthID = NULL

RETURN @MonthID

END;

Query
SELECT dbo.GetMonthID([Time Period NAME]) FROM dbo.TableName

SSIS Expression
Following expression can be used to return Month number in YYYYMM format for a month name in "MON-YYYY", "MON YYYY", "MON/YYYY" Formats.
(DT_I4)(SUBSTRING(Month,4,2) + RIGHT(Month,2) +
    (    FINDSTRING(Month,"JAN",1) > 0 ? "01" :
        FINDSTRING(Month,"FEB",1) > 0 ? "02" :
        FINDSTRING(Month,"MAR",1) > 0 ? "03" :
        FINDSTRING(Month,"APR",1) > 0 ? "04" :
        FINDSTRING(Month,"MAY",1) > 0 ? "05" :
        FINDSTRING(Month,"JUN",1) > 0 ? "06" :
        FINDSTRING(Month,"JUL",1) > 0 ? "07" :
        FINDSTRING(Month,"AUG",1) > 0 ? "08" :
        FINDSTRING(Month,"SEP",1) > 0 ? "09" :
        FINDSTRING(Month,"OCT",1) > 0 ? "10" :
        FINDSTRING(Month,"NOV",1) > 0 ? "11" :
        FINDSTRING(Month,"DEC",1) > 0 ? "12" :
        "00"
        )
    )