Sql server convert String time to Decimal

Asked

Viewed 1,246 times

-1

Guys I’m having a question of how to convert a column string that brings the time like '08:00:00' to decimal, just like Excel does, when it changes the format to number for example:

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

Thanks in advance

sql sql-server

  • And what is the conversion rule you want to implement?

  • Friends, the colleague’s doubt is pertinent yes, so much so that there is a similar question with the same negativity, but the answers have approvals, which indicates usefulness in the answers. @Josédiz first, welcome to the forum. Access this link and perform the appropriate tests with the solutions presented. https://stackoverflow.com/a/25526039/4616856

1 answer

0

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

follows 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

Browser other questions tagged sql sql-server

You are not signed in. Login or sign up in order to post.