In some industries labels must be in the format yyyy MM dd - ie. 2023 AL 26.
We deal with food and restricted substances and this is a requirement on many forms and labels.
There is likely an easier way but it comes up reasonably often at work so I thought I would share the code.
I used 3 calculated fields in the BAQ for ease of coding.
1) MfgMonth - integer - Month(PartLot.MfgDt)
2) MfgMonthCanNotation - nvarchar(8) -
case
when MfgMonth is NULL then ‘’
when MfgMonth = ‘’ then ‘’
when MfgMonth = 1 then ‘JA’
when MfgMonth = 2 then ‘FE’
when MfgMonth = 3 then ‘MR’
when MfgMonth = 4 then ‘AL’
when MfgMonth = 5 then ‘MA’
when MfgMonth = 6 then ‘JN’
when MfgMonth = 7 then ‘JL’
when MfgMonth = 8 then ‘AU’
when MfgMonth = 9 then ‘SE’
when MfgMonth = 10 then ‘OC’
when MfgMonth = 11 then ‘NO’
when MfgMonth = 12 then ‘DE’
else ‘’
end
I added the null check as PartLot is joined in and I’ve found is easier to check for nulls before a dashboard is in production rather than to try to ferret out strange results later.
3) MfgDate - nvarchar(10)
substring(convert(varchar, PartLot.MfgDt , 111),1,4) + ’ ’ + MfgMonthCanNotation + ’ ’ + substring(convert(varchar, PartLot.MfgDt , 111),9,2)