Compute addition of datetime fields

Question

Post reply

Compute addition of datetime fields

Sanchit297

Old Hand

Points: 348
More actions
May 25, 2010 at 11:07 am

#100729

Hi,
I have 2 fields - ScanTime and DataTime. Now I want manipulate the these 2 fields to get AverageTime something like..
(Sum of timestamps for ScanTime + Sum of timstamp for DataTime)% (Total events)
ScanTime and DataTime are DATETIME type and Total Events is INT.
Eg..
ScanTime - 2010-05-25 03:45:10
DataTime - 2010-06-23 09:50:09
AverageTime field should be in hh24:mi:ss format.
How can it be achieved ?
Thanks,

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Ron McCullough SSC Guru Points: 63877 More actions · Answer 1

Semingly endless T_SQL for various date time calculations. Read this by Lynn Pettis for help.

https://www.sqlservercentral.com/blogs/lynnpettis/archive/2009/03/25/some-common-date-routines.aspx

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

Sanchit297 Old Hand Points: 348 More actions · Answer 2

Thanks for your reply !

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

Sanchit297 Old Hand Points: 348 More actions · Answer 3

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.