SQL CONCAT drops zeros from expression

I have tested this and it returns the result you want:

select CASE WHEN mnt < 10  THEN 
                 concat('0' , mnt)
       ELSE mnt
       END  AS month_w_0
from ( select  month([Transact_Date]) mnt
       from test_t) A

I have realised what your problem is. YOu can do it this way too:

SELECT 
CASE
      WHEN month([Transact_Date]) < 10 
      THEN CONCAT(str(0),STR(month([Transact_Date]),1))
      ELSE STR(month([Transact_Date]),1)
      END  AS month_w_0
from test_t

The problem is only the else part and I believe that is because case when clause returns only one type od data. In your then part you have tryed to retunr string and in the else part number.

Hope this helps...

Here is a demo

I think a left padding trick is what you want here. Assuming your database be SQL Server:

SELECT RIGHT('00' + STR(MONTH([Transact_Date])), 2) AS month_w_0
FROM yourTable;

You don't need a CASE expression for this. In more recent versions of SQL Server, the FORMAT function might also be able to handle this.