Sql server convert String time to Decimal

-1

I have a question about how to convert a string column that returns the time as '08: 00: 00 'to decimal, exactly as Excel does, when changing the format to numero for example:

'08: 00: 00 '=' 0,33333333 '

Thank you in advance

    
asked by anonymous 18.10.2018 / 22:52

1 answer

0

You can divide the value by 86400 which is the number of seconds in the day, but before that you should convert to seconds

Here is a complete example:

Declare  @data As Datetime = '08:00:00'
        ,@H As Float
        ,@M As Float
        ,@S As Bigint
        ,@Result As Bigint

Set      @H = Datepart(hh,@data)*3600
Set      @M = Datepart(mi,@data)*60
Set      @s = Datepart(ss,@data)

Select   (@H + @M + @s) / 86400
    
18.10.2018 / 23:52