# Miscellaneous > Structured Query Language (SQL) >  Compute datetime field addition

## sunny297

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..



```
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.

----------


## SDas

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?

----------


## sunny297

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.

----------


## 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

----------


## SDas

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.

----------


## sunny297

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.

----------


## SDas

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.

----------


## sunny297

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.

----------


## sunny297

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.



```
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

----------


## sunny297

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.

----------

