asked on

Get sum of duration

Hi,

in VB.NET i want to create a sql statement for sql-server that gives me the duration.

Like this:
Record1:
FromDateTime= 07/04/2006 15:00
ToDateTime= 07/04/2006 15:10
FilterField=1
Record2:
FromDateTime= 07/04/2006 15:20
ToDateTime= 07/04/2006 15:25
FilterField=0
Record3:
FromDateTime= 07/04/2006 15:30
ToDateTime= 07/04/2006 15:50
FilterField=1

When i do this "SELECT <what to place here?> FROM Table WHERE Filterfield=1"
i should get something like
30minutes or even in seconds or milliseconds, i don't care about that, i just want this data out like that.
seconds, milliseconds,minutes,... are easy to convert to anything else.

ASKER CERTIFIED SOLUTION

Brian Mulder

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Joachim Carrein

ASKER

this gives me also datetimes, i have already tried this before

i just got to this, this seems to work fine:
SELECT DATEDIFF(s, FromDateTime, ToDateTime) AS Total
FROM table
WHERE Filterfield=1

Joachim Carrein

ASKER

Thanks for searching anyway, i give you the credit, but only C

Brian Mulder

well you could've left this open for a while there are others looking at it, and i was preparing a table to test with

Brian Mulder

i used this on a table here with fields that contain a datefield filled with a format

yyyy/mm/dd hh:mm:ss

select CONVERT(varchar(8), (MAX(tiqdate) - MIN(tiqdate)), 14) AS Duration from myTable
where id = 23991
and mydate between '20060410 09:15:00' and '20060410 09:35:00'

returning the hh:mm:ss part

00:17:00 which are 17 minutes between the 2 in the select