SQL Server add leading zero to single digit month

concatenationsqlsql-server

I'm trying to add a leading zero to months where there is only a single digit (everything before October). Here's my code:

with tab1 as (
    select month(CommitDate) monCommDt
            , year(CommitDate) yrCommDt
    from myTable

)

select CASE WHEN LEN(monCommDt) = 1
            THEN CONCAT('0', monCommDt)
            ELSE monCommDt
            END lzCommDt
        , yrCommDt
        , LEN(monCommDt) lenCommDt
from tab1

Here's the output:

lzCommDt    yrCommDt    lenCommDt
7   2013    1
7   2013    1
7   2013    1
7   2013    1

Any idea why the leading 0 isn't being added?

Best Solution

The reason why the leading 0 is not added is because the result of your CASE expression is converted back to INT. Remember when using a CASE expression, if the datatype of all the results is not the same, they are converted to the one with the higher data type precedence, in this case, an INT. You should CAST your results to VARCHAR to achieve the desired result:

CASE 
    WHEN LEN(monCommDt) = 1
        THEN CONCAT('0', CAST(monCommDt AS VARCHAR(2)))
    ELSE 
        CAST(monCommDt AS VARCHAR(2))
END lzCommDt

Another solution is to pad the result by using RIGHT:

SELECT RIGHT('0' + CAST(monCommDt AS VARCHAR(2)), 2)