I have some non-working code to concatenate two strings, one of which is a year value and the other is a month value, both of which I convert to strings from integers. The month further needs padded with leading zeros (January is 01, not 1).
So, this is a year-month field, and should read this to reflect January 2020:
202001
I was able to pad the month to 2 digits using this:
LEFT('00'+CAST(a.[Fiscal Month] as varchar(2)),2)) as [YearPeriod]
And this works on it's own. But once I concatenate it with the year, like this,
CAST(a.[Fiscal Year] as varchar(4)) + LEFT('00'+CAST(a.[Fiscal Month] as varchar(2)),2) as [Period]
Then I end up with no month at all. All the dates look like this:
202000
What am I doing wrong here? I've used various kinds of syntax ("concat" function as opposed to + as well as converting to the string after the padding vs converting first). I also tried padding with only 1 digit of zeros thinking I was cutting off the month number with zeros.
I have been formatting date fields and dealing with this, and I can't move on to the project I need to finish. Is the concatenation canceling out the month field somehow?
The field that this is going into is an nvarchar(6), so in my code I formatted the year portion to nvarchar(4) and the month to nvarchar(2), hoping that would just work being added together. Any other ideas?
I think this does what you want:
convert(varchar(6), a.[Fiscal Year] * 100 + a.[Fiscal Month])