Convertion of Year to month

Question

I have one column in my Resume table as YearsofExperience .but all data in year. but i want month.means i want to convert the year to month .

Shahriar Nikkhah · 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 Mukherjee · Answer

Use as like this: datename(month,( YearsofExperience )) as MONTH_YrOfExp

Salman Beg · Answer

declare @Year int declare @Month int Set @Year=5 set @Month=@Year*12 select @Month . Thanks.