Search code examples
sqlamazon-redshiftcase-when

add a leading zero case when

I am trying to add a leading zero to a week number if it's less than 10. Both col types are smallint and I've tried the following code which is not producing what I want. what I get:'20201' vs what I want '202001'.

SELECT CONCAT(CAST(yr_num AS VARCHAR), CASE WHEN CAST(wk_num AS VARCHAR) < 10 THEN '0' + wk_num ELSE wk_num  END) as year_wk FROM sometable

I am using redshift for this query.

Solution

  • You want lpad():

    select concat(yr_num, lpad(wk_num, 2, '0'))

    I don't think you need to convert the numbers for concat(), but I don't have Redshift on-hand to test.