Geek Answers Handbook

Transact-SQL Summarize Elapsed Time

I have a table in my database, the start and end time of the recording for a specific task. Here is an example of data:

Start                       Stop
9/15/2008 5:59:46 PM        9/15/2008 6:26:28 PM
9/15/2008 6:30:45 PM        9/15/2008 6:40:49 PM
9/16/2008 8:30:45 PM        9/15/2008 9:20:29 PM
9/16/2008 12:30:45 PM       12/31/9999 12:00:00 AM

I would like to write a script that sums up the elapsed minutes for this time frame, and where there is a date 12/31/9999, I want it to use the current date and time, as it is still in the process.

How do I do this using Transact-SQL?

+5
source share
8 answers
SELECT  SUM( CASE  WHEN Stop = '31 dec 9999' 
                   THEN DateDiff(mi, Start, Stop)
                   ELSE DateDiff(mi, Start, GetDate())
             END ) AS TotalMinutes 
FROM    task

However, the best solution would be to make Stop field nullable, and make it null when the task is still running. That way, you could do this:

SELECT SUM (DateDiff (mi, Start, IsNull (Stop, GetDate ())) AS TotalMinutes 
FROM task
+11
source

I think this is cleaner:

   SELECT  SUM(
               DATEDIFF(mi, Start, ISNULL(NULLIF(Stop,'99991231'), GetDate()))
              ) AS ElapsedTime
   FROM Table
+3
source

Try:

Select Sum(
    DateDiff(
        Minute,
        IsNull((Select Start where Start != '9999.12.31'), GetDate()),
        IsNull((Select End where End != '9999.12.31'), GetDate())
    )
)
from *tableName*
+2

SQL Server, .

Select  Case When (Stop <> '31 Dec 9999') Then 
          DateDiff(mi, Start, Stop) 
        Else 
          DateDiff(mi, Start, GetDate()) 
        End
From    ATable
+1

. if 12/31/9999 ; -)

0

- , mi

-

select  Start,  
        Stop, 
        CASE 
            WHEN Stop = '9999-12-31' THEN  datediff(ss, start,getdate())
            ELSE datediff(ss, start,stop) 
        END duration_in_seconds 

from mytable

- sum

Select Sum(duration_in_seconds)
from 
(
select  Start,  
        Stop, 
        CASE 
            WHEN Stop = '9999-12-31' THEN  datediff(ss, start,getdate())
            ELSE datediff(ss, start,stop) 
        END duration_in_seconds 

from mytable)x
0

Datediff , (.. , , ). , TSQL . , , :

select duration = stop - start

, , :

select convert (datetime, '2008-09-17 04: 56: 45.030') - convert (datetime, '2008-09-17 04: 53: 05.920')

"1900-01-01 00: 03: 39.110", , //; 3 , 39,11 . TimeSpan.Parse .

0

AJ answer, BelowNinety answer Nerdfest answer, :

Select Sum(
    Case When End = '12/31/9999 12:00:00 AM' Then
         DateDiff(mi, Start, Getdate()) 
    Else 
         DateDiff(mi, Start, End) 
    End) As ElapsedTime 
From Table

!

0

More articles:


All Articles