Compute datetime field addition

Hi,

I have 2 columns - ScanDateTime and DataDateTime and both are DATETIME fields having values as given below..

ScanDateTime DataDateTime
2009-10-21 08:51:22 2009-10-21 09:53:16
2009-10-21 10:19:27 2009-10-21 09:53:16
2009-10-21 10:19:27 2009-10-21 10:44:50
2009-10-21 10:44:50 2009-10-21 10:59:33
2009-10-21 10:59:33 2009-10-21 09:18:53

Now I want to add all the value in ScanDateTime field and in DataDateTime field such as..

Code:

---

Value = (Sum of timestamps for ScanDateTime + Sum of timestamps for DataDateTime)

---

And Divide the data returned from Value by a Number ( say 1000).

How can this be acheived ??

Thanks.

sunny297, what, exactly, do you want to accomplish?

Also, Date functions / manipulation is very database dependent. An answer with DB2 syntax won't work in SQL Server, for example. Can you mention what Database you are using?

Hi,

The platform I am working on is SQL Server 2000.

To rephrase my requirement again..

Value1 = Sum of all timestamp for ScanDateTime

Value2 = Sum of all timestamps for DataDateTime

Value3 = (Value1 + Value2)/1000

The datatype for Value1, Value2 and Value3 should be (hh24:mi:ss) format.

The datatype for ScanDateTime and DataDateTime columns is (yyyy-mm-dd hh24:mi:ss) format.

Looking forward for a prompt response.

Thanks.

Quote:

---

Originally Posted by sunny297

The datatype for Value1, Value2 and Value3 should be (hh24:mi:ss) format.

---

Sorry, it should be like - The datatype for Value3 should be (hh24:mi:ss) forma

Not quite there yet, sunny297. Of itself, summing Timestamps does not make any sense.

Are you trying to determine the total number of hours, minutes and/or seconds that ScanData took? (but then you would need a Start time and an End time)

What I need is what your are ultimately trying to accomplish by 'adding' timestamps.

Hi,

Yes - I want something like what you mentioned, find the total hh:mm:ss for ScanDateTime abd DataDateTime respectively.

It is one of the requirement in my project ( know it sounds bit off sense), but my client wants this columns (AverageTime or Value3) to be determined by the way ive just told you above. The client will use this field to get some other data ( which is out of my scope).

Hope it helps.

Thanks.

I am still not sure if I understand as you need a start time and and end time to get an elapsed time. I am going to assume that Scan Date is when a Scan started and Data Date is when the Scan finished. If that is the case, what you want to do is get the difference between the two, sum up this difference and then divide by your number. The final result will be in seconds but you should be able to get hours, minutes and seconds out of that.

This will return the number of Seconds between the start time of ScanDatetime and DateDatetime:

DATEDIFF(S,ScanDatetime, DataDatetime)

From there you should be able to add, subtract, average or do anything else you like with it.

If this is not what you want, and ScanDatetime and DateDatetime are not the starting and stopping times, you still need something to determine elapsed time.

Thanks for your answers so far !!

I already have a field to calculated ELapsed time. The field about which I have issues is the Averagetime (or Value3).

It needs to be the sum os ScanTime and DataTime divided by a number figure..

Thanks.

I used the below block to get the AverageTime field by adding the 2 date columns by first converting them to seconds and then dividing it with a number field.

Code:

---

begin

declare @date1 nvarchar(100), @date2 nvarchar(100), @FinalDate nvarchar(100), @count int

create table #temp6 (tks int, date1 nvarchar(100), date2 nvarchar(100), FinalDate nvarchar(100))

select @count = count(eventcode) from trackedplusimport_temp where deviceid=13768
SELECT @date1 = Convert(nvarchar(10),sum(DatePart(hh,ScanDateTime)))+':'+ Convert(nvarchar(10),sum(DatePart(mi,ScanDateTime)))+':'+ Convert(nvarchar(10),sum(DatePart(ss,ScanDateTime))) FROM trackedplusimport_temp WHERE deviceid=13768
SELECT @date2 = Convert(nvarchar(10),sum(DatePart(hh,DataDateTime)))+':'+ Convert(nvarchar(10),sum(DatePart(mi,DataDateTime)))+':'+ Convert(nvarchar(10),sum(DatePart(ss,DataDateTime))) FROM trackedplusimport_temp WHERE deviceid=13768

SELECT @FinalDate =
convert(nvarchar(10),
(
 (
        (DATEPART(hour, convert(nvarchar,@date1,108)) * 60 * 60) +
        (DATEPART(minute,convert(nvarchar,@date1,108)) * 60) +
        DATEPART(second, convert(nvarchar,@date1,108))
 )
 +
 (
        (DATEPART(hour, convert(nvarchar,@date2,108)) * 60 * 60) +
        (DATEPART(minute, convert(nvarchar,@date2,108)) * 60) +
        DATEPART(second, convert(nvarchar,@date2,108))
 )
)
)
--select @c = @b/@count

insert into #temp6 values(@count, @date1, @date2, @FinalDate)

end

--drop table #temp6
--select * from #temp6

---

When Executing the above, Im getting the below error..


Arithmetic overflow error converting expression to data type datetime


How can I overcome this error?

Thanks in advance

Just some more info..

When Date1 and Date2 are upto : '23:59:59', the query is working fine. But when it becomes '24:00:00' - I am getting Overflow error.