# Thread: Is there an aggregate function to sum a datetime field values?

1. Registered User
Join Date
Jan 2004
Posts
3

## Unanswered: Is there an aggregate function to sum a datetime field values?

Hi all...

how I can obtain the sum of a datetime field as aggregate function?

Given a set of records I need to calculate the number of records (count (*)) and the sum of a field of type datetime.
Is this possible? how?

Thanks..

Massimo

2. SQL Consultant
Join Date
Apr 2002
Location
Posts
20,002
massimo, i don't think you can do it, it doesn't make sense

please tell me, what is the sum of 2003-12-31 plus 1969-07-20 plus 1991-01-16 plus 1986-01-28?

thank you

3. Registered User
Join Date
Jan 2003
Location
India
Posts
523

## Re: Is there an aggregate function to sum a datetime field values?

Originally posted by maxdaros
Hi all...

how I can obtain the sum of a datetime field as aggregate function?

Given a set of records I need to calculate the number of records (count (*)) and the sum of a field of type datetime.
Is this possible? how?

Thanks..

Massimo
what do u mean by sum of dates?

4. Registered User
Join Date
Jan 2004
Posts
3
Hi,

I need to sum only hh:mm:ss fields, not all the data entry.
So, if I've 00:43:21 + 01:10:00, then I obtain 01:53:21...

All the datetime entry doesn't make sense.

Thanks

Massimo

5. SQL Consultant
Join Date
Apr 2002
Location
Posts
20,002
"All the datetime entry doesn't make sense."

yeah, but that's what you asked

you are using a datetime column to store intervals, i.e. hours and minutes?

why?

okay, this may not be the best approach, but here's what i would do

pull out the hours and minutes and seconds as substrings from a CONVERTed string, cast them as integers, calculate an expression in seconds

here's my test query --
Code:
```select convert(char(26),adate,109) as mydate
, cast(substring(convert(char(26),adate,109),13,2) as integer) * 3600
+ cast(substring(convert(char(26),adate,109),16,2) as integer) * 60
+ cast(substring(convert(char(26),adate,109),19,2) as integer)    as myseconds
from somedates
order by 1

mydate	                     myseconds
Feb  2 2002  9:37:00:000AM	34620
Jan  1 2001 11:11:00:000AM	40260
Jan  2 2001 12:34:00:000PM	45240
Jan  3 2001  3:03:00:000AM	10980
Jul  7 2007 12:00:00:000AM	43200
Sep  9 1949 12:00:00:000AM	43200```
now, you can sum up the seconds, and then figure out some way of representing the total --
Code:
```select sum(
+ cast(substring(convert(char(26),adate,109),16,2) as integer) * 60
) as totalseconds
, sum(
+ cast(substring(convert(char(26),adate,109),16,2) as integer) * 60
) / 60.00 as totalminutes
, sum(
+ cast(substring(convert(char(26),adate,109),16,2) as integer) * 60
) / 3600.00 as totalhours

from somedates
order by 1

totalseconds	totalminutes	totalhours
217500	3625.000000	60.4166666```
good luck

6. World Class Flame Warrior
Join Date
Jun 2003
Location
Ohio
Posts
12,595
This give the sum of the seconds since midnight for each record.

select sum(datediff(second, convert(char(10), YourDate, 120), YourDate)) TotalSeconds
from YourTable

7. SQL Consultant
Join Date
Apr 2002
Location
Posts
20,002
doh!

good one, blindman

8. Registered User
Join Date
Jan 2004
Posts
3
Hi,

I've used this function :
SUM ( DATEPART(hh, MyDateField) * 3600 + DATEPART(mi, MyDateField) * 60 + DATEPART(ss, MyDateField) )

now SQL returns the sum in seconds.....

and I use a java method called fromSecondsToStringData () in a java bean that I use to render the query result in a JSP page...

private String fromSecondsToStringDate ( int totalSeconds )
{
int hours = totalSeconds / 3600;
int minutes = ( totalSeconds % 3600 ) / 60;
int seconds = ( totalSeconds % 3600 ) % 60;

String hoursStr = new Integer ( hours ).toString ();
String minutesStr = new Integer ( minutes ).toString ();
String secondsStr = new Integer ( seconds ).toString ();

if ( hoursStr.length () < 2 )

if ( minutesStr.length () < 2 )
minutesStr = "0" + minutesStr;

if ( secondsStr.length () < 2 )
secondsStr = "0" + secondsStr;

return new String ( hoursStr + ":" + minutesStr + ":" + secondsStr );
}

and this appear to work fine.
Thanks

Massimo

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•