Skip to content
Advertisement

How can I concat 2 different numbers to look like a decimal but is actually a varchar without losing place in the decimal portion?

I’m trying to calculate Hours and Minutes, but the minutes portion is dropping the leading 0 and it appears the calculation is incorrect.

Example, 9.06 is displaying as 9.6 using this query against the values

ClockIn 2020-01-07 23:00:53.000
ClockOut 2020-01-08 07:06:52.000

This results in a calculated time of 8.6:

concat(datediff(n,ClockIn,ClockOut)/60,'.',
    case when len(datediff(n,ClockIn,ClockOut) - ((datediff(n,ClockIn,ClockOut)/60) * 60)) = 1 
        then concat(0,cast(datediff(n,ClockIn,ClockOut) - ((datediff(n,ClockIn,ClockOut)/60) * 60)as varchar(2)))
        else datediff(n,ClockIn,ClockOut) - ((datediff(n,ClockIn,ClockOut)/60) * 60)
    end 
)

Advertisement

Answer

Final version that works is as follows:

concat(cast(datediff(n,ClockIn,ClockOut)/60 as varchar(2)),'.',
    case when len(cast(datediff(n,ClockIn,ClockOut) - ((datediff(n,ClockIn,ClockOut)/60) * 60)as varchar(2))) = 1 
    then concat(0,cast(datediff(n,ClockIn,ClockOut) - ((datediff(n,ClockIn,ClockOut)/60) * 60)as varchar(2)))
    else cast(datediff(n,ClockIn,ClockOut) - ((datediff(n,ClockIn,ClockOut)/60) * 60) as varchar(2))
    end )
User contributions licensed under: CC BY-SA
9 People found this is helpful
Advertisement