DATEDIFF possibilities

Hi,

I have two fields in one of my database table. They are date fields, one for the start and the other for the end of the "problem solving". What I want to do is to show the concrete elapsed time between the two.

My first problem is that I'm not able to show hour AND minutes. I wrote this for instance:

SELECT DATEDIFF(hour, fld_date_debut, fld_date_fin) As elapsed_time

I tried HH:mm instead of hour, but it is not working.

My second problem is that I can have more than one start and end hour for the same "problem solving". In the database it's like:

start hour: 10:00 End hour: 11:00 Number of the problem: 1
start hour: 13:00 End hour: 16:00 Number of the problem: 1

So I would like to add these to my elapsed time. I want (11:00 - 10:00) + (16:00 - 13:00)...but how can i do this in my SQL query ?

Thanks.SQL Server is not particularly good at calculating elapsed times. The DATEDIFF function doesn't have a mask to return hours and minutes. My suggestion would be to determine the number of seconds between your dates and then calculate out the hours, minutes, and seconds.

Like this:

DECLARE @.StartDate datetime, @.EndDate datetime, @.ElapsedTime varchar(30)
SELECT @.StartDate = '20040701 11:39:00'
SELECT @.EndDate = '20040701 11:42:09'
SELECT @.ElapsedTime = DATEDIFF(s, @.StartDate, @.EndDate)
SELECT
@.ElapsedTime/3600 AS Hours,
(@.ElapsedTime % 3600)/60 AS Minutes,
@.ElapsedTime % 60 AS Seconds

So, for your needs I think you'd have something like this:

SELECT
ProblemNumber,
Elapsed/3600 AS Hours,
(Elapsed % 3600)/60 AS Minutes,
Elapsed % 60 AS Seconds
FROM
(
SELECT
ProblemNumber,
SUM(DATEDIFF(s,fld_date_debut,fld_date_fin)) AS Elapsed
FROM
YourTable
GROUP BY
ID
) AS SubQuery

Terri