cancel
Showing results for 
Search instead for 
Did you mean: 

Summing of the time fields

Former Member
0 Kudos

Hi all

I want to sum up the current value of the 3 different fields of Date/Hr datatype

Ex

a=1:10

b=2:56

c=3:34

d=abc = 7:40

thanks in advance.

Thanks & Regards

John

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

hi jensen

thanks for the reply, is it possible through query statement?

thanks & regards

john

rasmuswulff_jensen
Active Contributor
0 Kudos

Well not the most pretty piece of SQL, but it does the trick... I'm not an expert in SQL so there might be a better method of doing this:

The sample below use 3 userdefined fields on Business Partner U_T1, U_T2 and U_t3 all of type Time... Hope this helps...


DECLARE @CardCode AS VARCHAR(15)
SET @CardCode = '020010' --My testrow

DECLARE @T1 AS Varchar(4)
DECLARE @T2 AS Varchar(4)
DECLARE @T3 AS Varchar(4)
SET @T1 = (SELECT REPLICATE('0', 4-(LEN(U_T1)))+CAST(U_T1 AS VARCHAR(4)) FROM OCRD WHERE CardCode = @CardCode)
SET @T2 = (SELECT REPLICATE('0', 4-(LEN(U_T2)))+CAST(U_T2 AS VARCHAR(4)) FROM OCRD WHERE CardCode = @CardCode)
SET @T3 = (SELECT REPLICATE('0', 4-(LEN(U_T3)))+CAST(U_T3 AS VARCHAR(4)) FROM OCRD WHERE CardCode = @CardCode)

DECLARE @T1DATE AS DATETIME
DECLARE @T2DATE AS DATETIME
DECLARE @T3DATE AS DATETIME

SET @T1DATE = '1900-01-01 '+SUBSTRING(@T1,1,2)+':'+SUBSTRING(@T1,3,2)
SET @T2DATE = '1900-01-01 '+SUBSTRING(@T2,1,2)+':'+SUBSTRING(@T2,3,2)
SET @T3DATE = '1900-01-01 '+SUBSTRING(@T3,1,2)+':'+SUBSTRING(@T3,3,2)

DECLARE @FINALRESULT AS DATETIME
SET @FINALRESULT = (SELECT @T1DATE+@T2DATE+@T3DATE)

SELECT DATEPART(dd,@FINALRESULT)-1 AS Days, DATEPART(hh,@FINALRESULT) AS Hours, DATEPART(mi,@FINALRESULT) AS Mins

Answers (2)

Answers (2)

Former Member
0 Kudos

Hi jensen

thanks for the reply, it has given a rough idea, i will work on that

Once again thanks for the query.

Have a nice day

thanks & regards

John

rasmuswulff_jensen
Active Contributor
0 Kudos

If you use .Net you should use the TimeSpan object and the times as integers (database values)

1:10 = 110 = "0110" //(4 digit string)

2:56 = 256 = "0256" //(4 digit string)

3:34 = 334 = "0334" //(4 digit string)

The Code:


string a = "0110";
string b = "0256";
string c = "0334";

TimeSpan spanA = new TimeSpan(Convert.ToInt32(a.Substring(0, 2)), Convert.ToInt32(a.Substring(2, 2)), 0);
TimeSpan spanB = new TimeSpan(Convert.ToInt32(b.Substring(0, 2)), Convert.ToInt32(b.Substring(2, 2)), 0);
TimeSpan spanC = new TimeSpan(Convert.ToInt32(c.Substring(0, 2)), Convert.ToInt32(c.Substring(2, 2)), 0);
//Sum times
TimeSpan total = spanA + spanB + spanC;

This gives a new timespan with property Hour = 7 and property Minutes = 40