Leading 0 on int Column problem SQL Server

Question

I have an issue where I am trying to add a leading 0 to run an output.

SELECT 
    CASE 
        WHEN LEN(t.trans_time) = 5 
            THEN CONCAT(0, [trans_time])
            ELSE T.[trans_time] 
     END AS [TransactionTime]

     ,RIGHT(CONCAT(0,trans_time),6) AS trans_time

    ,LEN(T.trans_Time)

    ,t.trans_time

Why does the case statement not return the leading 0 whereas using:

,RIGHT(CONCAT(0,trans_time),6) AS trans_time

Works no problem.

Format output in the code which run sql query. Programming languages are much better tool for this then SQL. Sql wasn't designed for pretty output. — Fabio, Oct 05 '18 at 09:14
The case can only return one datatype, so make sure that all cases return varchar. Just convert the ELSE to varchar and it should work — GuidoG, Oct 05 '18 at 09:32
Maybe this can help you also https://stackoverflow.com/questions/5540064/padleft-function-in-t-sql — GuidoG, Oct 05 '18 at 09:34

score 4 · Accepted Answer · answered Oct 05 '18 at 09:09

4

Case expression return only one type, whereas concat() would return different type & i am assuming trans_time has INT type.

So, you would need to do type conversations :

SELECT (CASE WHEN LEN(t.trans_time) = 5 
             THEN CONCAT(0, [trans_time])
             ELSE CAST(T.[trans_time]  AS VARCHAR(255))
         END) AS [TransactionTime],
. . .

answered Oct 05 '18 at 09:09

Yogesh Sharma

49,870
5
26
52

So this would mean it will implicitly convert the concatenated values to a Varchar() – Mark Timson Oct 05 '18 at 09:30
Also the brackets around the CASE statement don't make a difference – Mark Timson Oct 05 '18 at 09:41
@MarkTimson. . . There is no brackets, there is two parenthesis one is opening `(` & other is closing `)` to make the code clear & readable. – Yogesh Sharma Oct 05 '18 at 09:42
Though this answers the question, I still wonder if the `format` function would not be better suited for this job – GuidoG Oct 05 '18 at 11:10
@GuidoG Both work just as well as eachother as I can see Thanks for your input. Up voting your solution. – Mark Timson Oct 05 '18 at 15:05

GuidoG · Answer 2 · 2018-10-05T09:58:16.240

Another way to do this is to use the format function, wich is available from sql server 2012.
It not only makes the code more readable but will also perform better.

declare @t table (id int)
insert into @t values (90113), (90204), (90207), (90235), (90302), (90318), (90324)

select format(id, '000000') as TransactionTime from @t

this will return

TransactionTime 
---------------
090113  
090204  
090207  
090235  
090302  
090318  
090324

Leading 0 on int Column problem SQL Server

2 Answers2