CODEWITHSUNDEEP
sqlsql-servert-sql
bmsqldev
bmsqldev

Reputation: 2735

Add the time values in SQL Server

We have an hour table in our application which stores the working hours for each associate. It has hour values as follow

0.30 , 
0.30 ,
1.10

0.30 indicates 30 minutes and 1.10 indicates 1 hour 10 minutes. So when I calculate the sum of hours I got 1.7, but I need to get 1.3 (I need to convert 1.10 to 0.70).

How to achieve this?

Upvotes: 0

Views: 74

Answers (4)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67281

Try it like this:

DECLARE @tbl TABLE(TimeValue VARCHAR(100));
INSERT INTO @tbl VALUES
 ('0.30'),('0.30'),('1.10');

WITH Splitted AS
(
    SELECT CAST('<x>' + REPLACE(TimeValue,'.','</x><x>') + '</x>' AS XML) AS vals
    FROM @tbl
)
,TheSplittedSums AS
(
    SELECT SUM(vals.value('/x[1]','int')) AS valHour
          ,SUM(vals.value('/x[2]','int')) AS valMinute
    FROM Splitted
)
SELECT valHour*60 + valMinute 
FROM TheSplittedSums

Delivers 130 (minutes)

Upvotes: 0

Dirk N
Dirk N

Reputation: 717

To convert 1.1 into 0.7

(int(1.1)*60+1.1%1*10)/100=.7

Upvotes: 0

Dyrandz Famador
Dyrandz Famador

Reputation: 4525

you can use some math calculation to achieve what you want .. 

SELECT workHours - FLOOR(workHours) + FLOOR(workHours)*0.60

sample value:

SELECT 1.10 - FLOOR(1.10) + FLOOR(1.10)* 0.60

result: 0.70

Upvotes: 1

user4196683
user4196683

Reputation:

A simple solution would be to store the time in minutes making arithmetic simple. Then in the presentation layer convert it to the desired format.

Upvotes: 0

Related Questions