CODEWITHSUNDEEP
sqlamazon-redshiftcase-when
greencode
greencode

Reputation: 57

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.

Upvotes: 1

Views: 5698

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269563

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.

Upvotes: 6

Related Questions