I have a data set that lists the Month and Year as 'MMM-YY' (ex: 'Jul-18') and need to convert this to 'YYMM' (ex: '1807'). Any ideas of a simple way to accomplish this in T-SQL?
asked
Convert 'MMM-YY' to 'YYMM'
Here's one way....
declare @YourTable table (
ReportingCategoryName nvarchar(50),
LuMMYY nvarchar(50),
InventoryValue float,
UnpaidCharges float,
TotalRevenue float,
NetExpenses float,
NetIncome float
)
insert into @YourTable select '0113','18-Jul',4684.91,22.32,1401.75,6944.31,-5542.56
insert into @YourTable select '0113','18-Aug',7858.71,127.3,50760.73,46704.67,4056.06
insert into @YourTable select '0113','18-Sep',6263.92,165.09,32531.55,30517.02,2014.53
insert into @YourTable select '0113','18-Oct',8360.12,146.7,51354.33,42388.95,8965.38
insert into @YourTable select '0113','18-Nov',5093.14,130.4,36870.46,36919.21,-48.75
insert into @YourTable select '0113','18-Dec',9557.56,199.59,33337.71,30951.43,2386.28
insert into @YourTable select '0113','19-Jan',7741.6,53.05,38083.58,41087.96,-3004.38
insert into @YourTable select '0113','YTD 2019',7079.99,120.64,244340.11,235513.55,8826.56
select
left(LuMMYY,2) +
right('00'+cast(datepart(month, cast('01 '+ right(LuMMYY,3) + left(LuMMYY,2) as date)) as varchar),2)
from @YourTable
where try_cast('01 '+ right(LuMMYY,3) + left(LuMMYY,2) as date) is not null
----
1807
1808
1809
1810
1811
1812
1901
(7 rows affected)
Can you post your table definition along with some sample data?
So your input data was in the format YY-MMM and not MMM-YY as originally stated, and you've got a row in there that doesn't convert - 'YTD 2019'
I've adjusted my answer above to handle these.
I'm not sure why the sample data came through as YY-MMM, but my data set is indeed in MMM-YY as originally indicated; the rest of the sample data is accurate. Thank you for your help thus far. I'm so close working with your previous examples, but I'm still having difficulty working around the conversion error when the date column is in MMM-YY format as below. The date/time functions are just not clicking with me yet. Any additional insight is much appreciated.
insert into @YourTable select '0113','Jul-18',4684.91,22.32,1401.75,6944.31,-5542.56
insert into @YourTable select '0113','Aug-18',7858.71,127.3,50760.73,46704.67,4056.06 insert into @YourTable select '0113','Sep-18',6263.92,165.09,32531.55,30517.02,2014.53 insert into @YourTable select '0113','Oct-18',8360.12,146.7,51354.33,42388.95,8965.38 insert into @YourTable select '0113','Nov-18',5093.14,130.4,36870.46,36919.21,-48.75 insert into @YourTable select '0113','Dec-18',9557.56,199.59,33337.71,30951.43,2386.28 insert into @YourTable select '0113','Jan-19',7741.6,53.05,38083.58,41087.96,-3004.38 insert into @YourTable select '0113','YTD 2019',7079.99,120.64,244340.11,235513.55,8826.56
19 People are following this question.
