question

mgiusto avatar image
mgiusto asked

How to add leading zero to concat year & month field

I have two smallint fields, one named mktg_year and one named mktg_month. In a select statement I want to concatenate both fields together, but need to add a leading zero to the mktg_month for values 1 thru 9. select convert(varchar(4), mktg_year) + convert(varchar(2), mktg_month) AS mktg_yyyymm This will give me 20131 which I would need to become 201301. Thanks for any help.
concatenation
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Kev Riley avatar image
Kev Riley answered
Use the RIGHT function to return only the rightmost 2 characters of a string where '0' is appended to the start: select convert(varchar(4), mktg_year) + right('0'+ convert(varchar(2), mktg_month),2) AS mktg_yyyymm for single digit months, this keeps the leading 0, for 2 digit months, it is stripped off.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.