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.
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.