Convert Time To Decimal In Sql Server 2012

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.

Solution 1:

You could do arithmetic such as:

selectcast(datepart(hour, col) + datepart(minute, col) /100.00asdecimal(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:

selectcast(datepart(hour, col) + datepart(minute, col) /60.00asdecimal(5, 2));

Solution 2:

You know that decimal value of 1:45:00 is 1.75h

try this if you want to convert time to decimal

select  datediff(second,0,'01:45:00') / (60.0*60.0)

But if you getting value as par your requirement then try this way

selectcast(datepart(hour, '01:45:00')+datepart(minute, '01:45:00') /100.00asdecimal(7,4));

Solution 3:

From the question, it seems you only want to remove the ':' with '.'. If so, it can be achieved in many ways in SQL Server 2012. One example is below.

If your column is 'Datetime', use this:

SELECT REPLACE(LEFT(CAST(DatetimeCol ASTIME),5),':',',') FROM Tbl

Else if it just 'Time' then remove the cast.