SQL: get DATEDIFF to not return negative values
Solution 1
Try these
Assuming 2 declared dates.
declare @start datetime
set @start = '2008-11-02 15:59:59.790'
declare @end datetime
set @end = '2008-11-02 19:05:41.857'
This will return the hours / mins / seconds
select
(datediff(ss, @start, @end) / 3600),
(datediff(ss, @start, @end) / 60) % 60,
(datediff(ss, @start, @end) % 60) % 60
--returns
----------- ----------- -----------
3 5 42
This is the zero-padded concatenated string version
select
RIGHT('0' + CONVERT(nvarchar, (datediff(ss, @start, @end) / 3600)), 2) + ':' +
RIGHT('0' + CONVERT(nvarchar, (datediff(ss, @start, @end) / 60) % 60), 2) + ':' +
RIGHT('0' + CONVERT(nvarchar, (datediff(ss, @start, @end) % 60) % 60), 2)
--------
03:05:42
Solution 2
Here's a way to do it:
-- Find Hours, Minutes and Seconds in between two datetime
DECLARE @First datetime
DECLARE @Second datetime
SET @First = '04/02/2008 05:23:22'
SET @Second = getdate()
SELECT DATEDIFF(day,@First,@Second)*24 as TotalHours,
DATEDIFF(day,@First,@Second)*24*60 as TotalMinutes,
DATEDIFF(day,@First,@Second)*24*60*60 as TotalSeconds
Solution 3
You need to be consistent with your calls to datediff(). They should all use the same datepart argument.
See MSDN's DATEDIFF (Transact-SQL) article.
In your example, you're using both "mi" and "hh" and concatenating.
Choose the least common denominator for your durations (probably ss or s) and do any math based on that (as the other answers are illustrating, but not really describing).
wergeld
Updated on June 05, 2022Comments
-
wergeld about 2 years
I have a query in which I am pulling the runtime of an executable. The database contains its start time and its end time. I would like to get the total time for the run. So far I have:
SELECT startTime, endTime, cast(datediff(hh,starttime,endtime) as varchar) +':' +cast(datediff(mi,starttime,endtime)-60*datediff(hh,starttime,endtime) as varchar) AS RUNTIME FROM applog WHERE runID = 33871 ORDER BY startTime DESC
When I execute this I get expected values and also some unexpected. For example, if starttime = 2008-11-02 15:59:59.790 and endtime = 2008-11-02 19:05:41.857 then the runtime is = 4:-54. How do I get a quere in MS SQL SMS to return the value 3:06 for this case?
Thanks.
Eoin Campbell's I selected as the answer is the most bulletproof for my needs. David B's is do-able as well.