time to decimal - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

time to decimal

Author

Topic

pnasz
Posting Yak Master

101 Posts

Posted - 2010-12-05 : 03:14:52

I have a column which stores hours & minutes (HH:MM) as computed column. each data entry would be similar to this format:
1:45
(represents 1 hour and 45 minutes).

I need to return the total no of hours from this field using SUM but need to convert the time representation to a decimal value. So for example:

1.45 (1 hour and 45 minutes)
3.20 (3 hours and 20 minutes)
2.40 (2 hours and 40 minutes)

Any help appreciated.

malpashaa
Constraint Violating Yak Guru

264 Posts

Posted - 2010-12-05 : 03:30:57

Try something like this:


DECLARE @SomeTable TABLE
(
   hours_and_minutes VARCHAR(5) NOT NULL
);

INSERT INTO @SomeTable(hours_and_minutes)
   VALUES('1:45'),
         ('3:20'),
         ('2:40');

SELECT SUM(DATEDIFF(MINUTE, 0, hours_and_minutes)) / 60.0
  FROM @SomeTable

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-12-11 : 07:48:52

SELECT REPLACE(CONVERT(CHAR(5), DATEADD(SECOND, DATEDIFF(SECOND, EmpSin, EmpSout), 0), 108), ':', '.')

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources