Sql – convert time to decimal in SQL server 2012

decimalsqltime

i have a table in sql server 2012, with one of its column having the following data in time datatype,
How would you convert 00:45:00 to 0.45
or 01:45:00 to 1.45 ?

please help.

Best Solution

You could do arithmetic such as:

 select cast(datepart(hour, col) + datepart(minute, col) / 100.00 as decimal(5, 2));

But as noted in the comments, "1.45" seems quite confusing. Most people think this should be "1.75". If you want the latter -- with precision to the minute -- you can do:

 select cast(datepart(hour, col) + datepart(minute, col) / 60.00 as decimal(5, 2));