Search code examples
sql-servertimessmsconcatenation

Convert number to 0 padded string

CASE 
   WHEN LEN(TIME1) = 3 THEN (RIGHT('0' + CAST(TIME1 AS VARCHAR(4)), 4)) 
END AS TIME1

This code works, because when value is 812, it will return 0812. But sometimes it is 1012, so the value is returned as null.

I wrote

CASE 
   WHEN LEN(TIME1) = 3 
      THEN (RIGHT('0' + CAST(TIME1 AS VARCHAR(4)), 4)) 
      ELSE TIME1 
END AS TIME1

But this just gives me 812 and 1012 all over again.

I am trying to convert it to 4 char so I can convert it to hh:mm and then concat with date column.

Solution

  • If you're using SQL Server (based on the SSMS tag) then the "normal" way to get a number with leading zeroes would be like this:

    RIGHT('0000'+CONVERT(varchar(4),YourColumn),4)

    You could use REPLICATE for a more scalable, or parametrisable, version:

    RIGHT(REPLICATE('0',4)+CONVERT(varchar(4),YourColumn),4)

    You can also use CONCAT to avoid to explicit conversion:

    RIGHT(CONCAT(REPLICATE('0',4),YourColumn),4)