Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 425
  • Last Modified:

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.
0
Joachim Carrein
Asked:
Joachim Carrein
  • 3
  • 2
1 Solution
 
bruintjeCommented:
Hello joachimcarrein,

you could use

SELECT (ToDateTime - FromDateTime) AS Duration FROM Table WHERE Filterfield=1

hope this helps a bit
bruintje
0
 
Joachim CarreinSr. Software DeveloperAuthor Commented:
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
0
 
Joachim CarreinSr. Software DeveloperAuthor Commented:
Thanks for searching anyway, i give you the credit, but only C
0
 
bruintjeCommented:
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
0
 
bruintjeCommented:
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
0

Featured Post

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now