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.

回答1:

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 AS TIME),5),':',',') FROM Tbl

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

回答2:

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));

回答3:

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

select cast(datepart(hour, '01:45:00')+datepart(minute, '01:45:00') / 100.00 as decimal(7,4));