Convertion of Year to month

I am just providing you with a script that has lots of good stuff related to month

It is in SQL

------------------------------------------------------------------------

--- Month

------------------------------------------------------------------------

-- SmallInt /*tinyint*/ (2)

, [MonthKey] = CAST (MONTH(D.[Day Date]) AS SmallInt /*tinyint*/)

-- Char(2) (02)

, [Month Number] = CAST (FORMAT(MONTH(D.[Day Date]), '00') AS Char(2))

-- nVarChar(30) (January,February,March, etc... or you can use ---- FORMAT(D.[Day Date], 'MMMM', 'en-US')

, [Month Name] = DATENAME(Month , D.[Day Date])

-- Char(3) (Jan , Feb , Mar ,...)

, [Month Name Short] = CAST (FORMAT(D.[Day Date], 'MMM') AS Char(3))

-- Char(8) (01 - Jan , 02 - Feb , 03 - Mar ,...)

, [Month Name Short4] = CAST (FORMAT(MONTH(D.[Day Date]), '00') AS Char(2)) + ' - ' + CAST (FORMAT(D.[Day Date], 'MMM') AS Char(3))

-- .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. ..

-- Char(2) (JA , FE , MR ,...)

, [Month Name Short2] = CHOOSE ( MONTH(D.[Day Date])

, 'JA' , 'FE' , 'MR' , 'AL'

, 'MA' , 'JN' , 'JL' , 'AU'

, 'SE' , 'OC' , 'NO' , 'DE'

)

-- .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. ..

-- Char(2) (JA , FE , MR ,...)

, [Month Name Short3] = CHOOSE ( MONTH(D.[Day Date])

, '01 - JA' , '02 - FE' , '03 - MR' , '04 - AL'

, '05 - MA' , '06 - JN' , '07 - JL' , '08 - AU'

, '09 - SE' , '10 - OC' , '11 - NO' , '12 - DE'

)

-- .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. ..

-- [Int] (201502)-- need to be [int] -- can't use --> FORMAT(D.[Day Date], 'yyyyMM', 'en-US')

, [MonthYearKey] = YEAR(D.[Day Date]) * 100 + MONTH(D.[Day Date])

-- Char(7) (201502)

, [Month Year Name] = CAST(CONCAT(YEAR(D.[Day Date]) , N'-' , FORMAT(MONTH(D.[Day Date]), '00')) AS Char(7) )

-- Char(8) (2015-Feb)

, [Month Name Year] = CAST(CONCAT(YEAR(D.[Day Date]) , N'-' , FORMAT(D.[Day Date], 'MMM')) AS Char(8) )

-- .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. ..

-- Char(7) (2015-JA , 2015-FE ,....)

, [Month Name Year2] = CAST (CONCAT(YEAR(D.[Day Date]) , N'-' , CHOOSE (MONTH(D.[Day Date])

, 'JA' , 'FE' , 'MR' , 'AL'

, 'MA' , 'JN' , 'JL' , 'AU'

, 'SE' , 'OC' , 'NO' , 'DE' )

) AS Char(7)

)

-- .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. ..

-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --

-- -- START/END Month and Next/Previous Month--

-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --

-- [Int] (20160201)

, [MonthStartDateKey] = CAST(CONVERT (nVarChar(8) , DATEADD(DAY, -DAY(D.[Day Date])+1, D.[Day Date]) , 112 ) AS Int)

-- [Date] (2015-02-01) --- or u can use --->>>>> , DATEADD(M, DATEDIFF(M, 0, D.[Day Date]), 0) AS NewDate

, [Month Start Date] = DATEADD(DAY, -DAY(D.[Day Date])+1, D.[Day Date])

-- [Int] (20160228)

, [MonthEndDateKey] = CAST(FORMAT(EOMONTH (D.[Day Date]) , 'yyyyMMdd') AS Int)

-- [Date] (2016-02-28)

, [Month End Date] = EOMONTH (D.[Day Date])

-- [Int] (28 ,29 ,30 , 31 ) Used In DAX mainly

, [Month End Days] = CAST(DAY(EOMONTH (D.[Day Date])) AS TinyInt)

-- [Date] (2016-03-31)

, [Next Month End Date] = EOMONTH (D.[Day Date] , + 1 )

-- [Date] (2016-01-31)

, [Previous Month End Date] = EOMONTH (D.[Day Date] , - 1 )

-- .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. ..

-- [Bit] (0 or 1)

, [Month Start Date Flag] = CASE WHEN DATEADD(DAY, -DAY(D.[Day Date])+1, D.[Day Date]) = D.[Day Date] THEN CAST(1 AS Bit)

ELSE CAST(0 AS Bit)

END

-- [Bit] (0 or 1)

, [Month End Date Flag] = CASE WHEN EOMONTH (D.[Day Date]) = D.[Day Date] THEN CAST(1 AS Bit)

ELSE CAST(0 AS Bit)

END

-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Shahriar Nikkhah7y ago
Accepted answer
Hi
I am just providing you with a script that has lots of good stuff related to month
It is in SQL

------------------------------------------------------------------------
--- Month
------------------------------------------------------------------------
-- SmallInt /*tinyint*/ (2)
, [MonthKey] = CAST (MONTH(D.[Day Date]) AS SmallInt /*tinyint*/)
-- Char(2) (02)
, [Month Number] = CAST (FORMAT(MONTH(D.[Day Date]), '00') AS Char(2))
-- nVarChar(30) (January,February,March, etc... or you can use ---- FORMAT(D.[Day Date], 'MMMM', 'en-US')
, [Month Name] = DATENAME(Month , D.[Day Date])
-- Char(3) (Jan , Feb , Mar ,...)
, [Month Name Short] = CAST (FORMAT(D.[Day Date], 'MMM') AS Char(3))
-- Char(8) (01 - Jan , 02 - Feb , 03 - Mar ,...)
, [Month Name Short4] = CAST (FORMAT(MONTH(D.[Day Date]), '00') AS Char(2)) + ' - ' + CAST (FORMAT(D.[Day Date], 'MMM') AS Char(3))
-- .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. ..
-- Char(2) (JA , FE , MR ,...)
, [Month Name Short2] = CHOOSE ( MONTH(D.[Day Date])
, 'JA' , 'FE' , 'MR' , 'AL'
, 'MA' , 'JN' , 'JL' , 'AU'
, 'SE' , 'OC' , 'NO' , 'DE'
)
-- .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. ..
-- Char(2) (JA , FE , MR ,...)
, [Month Name Short3] = CHOOSE ( MONTH(D.[Day Date])
, '01 - JA' , '02 - FE' , '03 - MR' , '04 - AL'
, '05 - MA' , '06 - JN' , '07 - JL' , '08 - AU'
, '09 - SE' , '10 - OC' , '11 - NO' , '12 - DE'
)
-- .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. ..
-- [Int] (201502)-- need to be [int] -- can't use --> FORMAT(D.[Day Date], 'yyyyMM', 'en-US')
, [MonthYearKey] = YEAR(D.[Day Date]) * 100 + MONTH(D.[Day Date])
-- Char(7) (201502)
, [Month Year Name] = CAST(CONCAT(YEAR(D.[Day Date]) , N'-' , FORMAT(MONTH(D.[Day Date]), '00')) AS Char(7) )
-- Char(8) (2015-Feb)
, [Month Name Year] = CAST(CONCAT(YEAR(D.[Day Date]) , N'-' , FORMAT(D.[Day Date], 'MMM')) AS Char(8) )
-- .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. ..
-- Char(7) (2015-JA , 2015-FE ,....)
, [Month Name Year2] = CAST (CONCAT(YEAR(D.[Day Date]) , N'-' , CHOOSE (MONTH(D.[Day Date])
, 'JA' , 'FE' , 'MR' , 'AL'
, 'MA' , 'JN' , 'JL' , 'AU'
, 'SE' , 'OC' , 'NO' , 'DE' )
) AS Char(7)
)
-- .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. ..
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
-- -- START/END Month and Next/Previous Month--
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
-- [Int] (20160201)
, [MonthStartDateKey] = CAST(CONVERT (nVarChar(8) , DATEADD(DAY, -DAY(D.[Day Date])+1, D.[Day Date]) , 112 ) AS Int)
-- [Date] (2015-02-01) --- or u can use --->>>>> , DATEADD(M, DATEDIFF(M, 0, D.[Day Date]), 0) AS NewDate
, [Month Start Date] = DATEADD(DAY, -DAY(D.[Day Date])+1, D.[Day Date])
-- [Int] (20160228)
, [MonthEndDateKey] = CAST(FORMAT(EOMONTH (D.[Day Date]) , 'yyyyMMdd') AS Int)
-- [Date] (2016-02-28)
, [Month End Date] = EOMONTH (D.[Day Date])
-- [Int] (28 ,29 ,30 , 31 ) Used In DAX mainly
, [Month End Days] = CAST(DAY(EOMONTH (D.[Day Date])) AS TinyInt)
-- [Date] (2016-03-31)
, [Next Month End Date] = EOMONTH (D.[Day Date] , + 1 )
-- [Date] (2016-01-31)
, [Previous Month End Date] = EOMONTH (D.[Day Date] , - 1 )
-- .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. ..
-- [Bit] (0 or 1)
, [Month Start Date Flag] = CASE WHEN DATEADD(DAY, -DAY(D.[Day Date])+1, D.[Day Date]) = D.[Day Date] THEN CAST(1 AS Bit)
ELSE CAST(0 AS Bit)
END
-- [Bit] (0 or 1)
, [Month End Date Flag] = CASE WHEN EOMONTH (D.[Day Date]) = D.[Day Date] THEN CAST(1 AS Bit)
ELSE CAST(0 AS Bit)
END
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
Sourav Mukherjee7y ago
Use as like this:

datename(month,(YearsofExperience)) as MONTH_YrOfExp
Salman Beg7y ago
declare @Year int declare @Month int Set @Year=5 set @Month=@Year*12 select @Month . Thanks.