Summing time......

[Paul Aspinall]

Hi
I have a field which is a Date/Time field....
It actually only makes use of the Time portion, and this holds a duration of
an activity....

However, I want to have a query which returns the total duration of all the
times in the records.
Is there an easy way, to SUM date/time fields (or just the time elements),
to give the total duration of all the records??

Thanks


Paul

[Nigel Rivett]

select sum(dtefld)

a datetime is just a decimal number with the integer part the date and
the decimal part the time. As you are just using the time the integer
part will be 0 (19000101) and you can just sum the fields to give a
total time.



Nigel Rivett (SQL Server MVP)
[url]www.nigelrivett.net[/url]

*** Sent via Developersdex [url]http://www.developersdex.com[/url] ***
Don't just participate in USENET...get rewarded for it!

[Nigel Rivett]

select sum(dtefld)

a datetime is just a decimal number with the integer part the date and
the decimal part the time. As you are just using the time the integer
part will be 0 (19000101) and you can just sum the fields to give a
total time.

except that sum will not take a datetime so you can

select convert(datetime,sum(convert(decimal(18,5),dtefld) ))
But I suspect that this will lose some precision.

But maybe better to take advantage of the factr that a date is 2 4 byte
integer values - the second being the time so

select
convert(datetime,convert(binary(8),sum(convert(int ,convert(binary(4),
dtefld))))) from #a


Nigel Rivett (SQL Server MVP)
[url]www.nigelrivett.net[/url]

*** Sent via Developersdex [url]http://www.developersdex.com[/url] ***
Don't just participate in USENET...get rewarded for it!