Calculating with datetime fieldtypes

Hello,
I'm calculating with 'datetime' fields and have the following
question/problem.
To get a period I subtract the field <Timefrom> from the field
<Timetill>. Both field have the type 'datetime'.
2005-09-22 15:00:00.000 - 2005-09-22 13:30:00.000
= 1900-01-01 01:30:00.000
This is working OK.
After this I would like to sum the results.
Then I get the message:
[Server: Msg 409, Level 16, State 2, Line 1
The sum or average aggregate operation cannot take a datetime data type
as an argument.]
How can I solve this, keeping the time (hours and minutes)?
NB. In my situation the period is smaller than 1 day.
Hans
*** Sent via Developersdex http://www.examnotes.net ***Take a look a DATEDIFF system function.
"Hans" <nospam@.devdex.com> wrote in message
news:%23Ma0eF2vFHA.2728@.TK2MSFTNGP14.phx.gbl...
> Hello,
> I'm calculating with 'datetime' fields and have the following
> question/problem.
> To get a period I subtract the field <Timefrom> from the field
> <Timetill>. Both field have the type 'datetime'.
> 2005-09-22 15:00:00.000 - 2005-09-22 13:30:00.000
> = 1900-01-01 01:30:00.000
> This is working OK.
> After this I would like to sum the results.
> Then I get the message:
> [Server: Msg 409, Level 16, State 2, Line 1
> The sum or average aggregate operation cannot take a datetime data type
> as an argument.]
> How can I solve this, keeping the time (hours and minutes)?
> NB. In my situation the period is smaller than 1 day.
> Hans
> *** Sent via Developersdex http://www.examnotes.net ***|||SELECT
DATEADD(MI,SUM(DATEDIFF(MI,timefrom,time
till)),0)
FROM YourTable ;
or
SELECT CONVERT(CHAR(5),
DATEADD(MI,SUM(DATEDIFF(MI,timefrom,time
till)),0),14)
FROM YourTable ;
David Portas
SQL Server MVP
--|||Hwo about some custom formatting for that, and the use of datediff:
CREATE FUNCTION Formatminutes
(
@.Minutes INT
)
RETURNS VARCHAR(200)
AS
BEGIN
DECLARE @.Hours INT
SET @.Hours = @.minutes/60
SEt @.Minutes = @.Minutes - @.hours * 60
RETURN (Select CAST(@.hours AS VARCHAR(10)) + ':' + RIGHT('00' +
CAST(@.Minutes AS VARCHAR(10)),2))
END
Select dbo.Formatminutes(ABS(Datediff(mi,'2005-09-22
15:01:00.000','2005-09-22 13:30:00.000')))
HTH, jens Suessmeyer.|||try this
SELECT FLOOR(SUM(DATEDIFF(SS,startdate,enddate)
)/60) as Minitues,
SUM(DATEDIFF(SS,startdate,enddate))%60 as seconds FROM <tablename>
use can straigh away use 'm' in place of ss also in the first one
Regards
R.D
"Uri Dimant" wrote:

> Take a look a DATEDIFF system function.
>
> "Hans" <nospam@.devdex.com> wrote in message
> news:%23Ma0eF2vFHA.2728@.TK2MSFTNGP14.phx.gbl...
>
>