<p>I have a table in my database the records start and stop times for a specific task.  Here is a sample of the data:</p>

<pre><code>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
</code></pre>

<p>I would like to write a script that totals up the elapsed minutes for these time frames, and wherever there is a 12/31/9999 date, I want it to use the current date and time, as this is still in progress.</p>

<p>How would I do this using Transact-SQL?</p>

[{'answer_id': 81928, 'author': 'Paul van Brenk', 'author_id': 1837197, 'author_profile': 'https://Stackoverflow.com/users/1837197', 'pm_score': 0, 'selected': False, 'text': '<p>The <a href="http://msdn.microsoft.com/en-us/library/ms189794.aspx" rel="nofollow noreferrer">datediff function</a> can display the elapsed minutes. The if statement for the 12/31/9999 check I\'ll leave as an excercise for the reader ;-)</p>\n'}, {'answer_id': 81981, 'author': 'Community', 'author_id': -1, 'author_profile': 'https://Stackoverflow.com/users/-1', 'pm_score': 0, 'selected': False, 'text': "<p>--you can play with the datediff using mi for minutes</p>\n\n<p>-- this give you second of each task</p>\n\n<pre><code>select  Start,  \n        Stop, \n        CASE \n            WHEN Stop = '9999-12-31' THEN  datediff(ss, start,getdate())\n            ELSE datediff(ss, start,stop) \n        END duration_in_seconds \n\nfrom mytable\n</code></pre>\n\n<p>-- sum</p>\n\n<pre><code>Select Sum(duration_in_seconds)\nfrom \n(\nselect  Start,  \n        Stop, \n        CASE \n            WHEN Stop = '9999-12-31' THEN  datediff(ss, start,getdate())\n            ELSE datediff(ss, start,stop) \n        END duration_in_seconds \n\nfrom mytable)x\n</code></pre>\n"}, {'answer_id': 81983, 'author': 'tsilb', 'author_id': 11112, 'author_profile': 'https://Stackoverflow.com/users/11112', 'pm_score': 0, 'selected': False, 'text': '<p>Datediff becomes more difficult to use as you have more dateparts in your difference (i.e. in your case, looks like minutes and seconds; occasionally hours).  Fortunately, in most variations of TSQL, you can simply perform math on the dates.  Assuming this is a date field, you can probably just query:</p>\n\n<p>select duration = stop - start</p>\n\n<p>For a practical example, let\'s select the difference between two datetimes without bothering with a table:</p>\n\n<p>select convert(datetime,\'2008-09-17 04:56:45.030\') - convert(datetime,\'2008-09-17 04:53:05.920\')</p>\n\n<p>which returns "1900-01-01 00:03:39.110", indicating there are zero years/months/days; 3 mins, 39.11 seconds between these two datetimes.  From there, your code can TimeSpan.Parse this value.</p>\n'}, {'answer_id': 82039, 'author': 'Nerdfest', 'author_id': 7855, 'author_profile': 'https://Stackoverflow.com/users/7855', 'pm_score': 2, 'selected': False, 'text': "<p>Try:</p>\n\n<pre><code>Select Sum(\n    DateDiff(\n        Minute,\n        IsNull((Select Start where Start != '9999.12.31'), GetDate()),\n        IsNull((Select End where End != '9999.12.31'), GetDate())\n    )\n)\nfrom *tableName*\n</code></pre>\n"}, {'answer_id': 82100, 'author': 'Martynnw', 'author_id': 5466, 'author_profile': 'https://Stackoverflow.com/users/5466', 'pm_score': 1, 'selected': False, 'text': "<p>The following will work for SQL Server, other databases use different functions for date calculation and getting the current time.</p>\n\n<pre><code>Select  Case When (Stop &lt;&gt; '31 Dec 9999') Then \n          DateDiff(mi, Start, Stop) \n        Else \n          DateDiff(mi, Start, GetDate()) \n        End\nFrom    ATable\n</code></pre>\n"}, {'answer_id': 83005, 'author': 'AJ.', 'author_id': 7211, 'author_profile': 'https://Stackoverflow.com/users/7211', 'pm_score': 5, 'selected': True, 'text': "<pre><code>SELECT  SUM( CASE  WHEN Stop = '31 dec 9999' \n                   THEN DateDiff(mi, Start, Stop)\n                   ELSE DateDiff(mi, Start, GetDate())\n             END ) AS TotalMinutes \nFROM    task\n</code></pre>\n\n<p>However, a better solution would be to make the <code>Stop field nullable, and make it null when the task is still running.  That way, you could do this: </p>\n\n<pre>SELECT  SUM( DateDiff( mi, Start, IsNull(Stop, GetDate() ) ) AS TotalMinutes \nFROM    task\n</code></pre>\n"}, {'answer_id': 86813, 'author': 'mattruma', 'author_id': 1768, 'author_profile': 'https://Stackoverflow.com/users/1768', 'pm_score': 0, 'selected': False, 'text': '<p>Using help from AJ\'s <a href="https://stackoverflow.com/questions/81905/transact-sql-to-sum-up-elapsed-time#83005">answer</a>, BelowNinety\'s <a href="https://stackoverflow.com/questions/81905/transact-sql-to-sum-up-elapsed-time#82039">answer</a> and Nerdfest\'s <a href="https://stackoverflow.com/questions/81905/transact-sql-to-sum-up-elapsed-time#82039">answer</a>, I came up with the following:</p>\n\n<pre><code>Select Sum(\n    Case When End = \'12/31/9999 12:00:00 AM\' Then\n         DateDiff(mi, Start, Getdate()) \n    Else \n         DateDiff(mi, Start, End) \n    End) As ElapsedTime \nFrom Table\n</code></pre>\n\n<p>Thanks for the help!</p>\n'}, {'answer_id': 136859, 'author': 'GilM', 'author_id': 10192, 'author_profile': 'https://Stackoverflow.com/users/10192', 'pm_score': 2, 'selected': False, 'text': "<p>I think this is cleaner:</p>\n\n<pre><code>   SELECT  SUM(\n               DATEDIFF(mi, Start, ISNULL(NULLIF(Stop,'99991231'), GetDate()))\n              ) AS ElapsedTime\n   FROM Table\n</code></pre>\n"}]
['https://Stackoverflow.com/questions/81905', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/1768/']