• Status: Solved
• Priority: Medium
• Security: Public
• Views: 344

# Difference between two dates

How do i calculate difference between two dates ? I need number of days, hours ,minutes and seconds.

But with this i get an issue when the date difference is negative or null ...

SELECT [HH:MM:SS] = IsNULL(CASE WHEN AVG(DateDiff(s,B.DateCreated,B.DateCompleted))/3600<10 THEN ''0'' ELSE '''' END +
RTrim(AVG(DateDiff(s, B.DateCreated, B.DateCompleted)) / 3600)  + '':'' +
RIGHT(''0''+RTRIM((AVG(DateDiff(s,B.DateCreated,B.DateCompleted)) % 3600) / 60),2)  + '':'' +
RIGHT(''0''+RTRIM((AVG(DateDiff(s,B.DateCreated,B.DateCompleted)) % 3600) % 60),2),''00:00:00'')
FROM tblprojects
0
byte1
• 2
1 Solution

Commented:
i couldn't fully understand your question... if you want to find days, hours ,minutes and seconds as separately...

use this...

SELECT DATEDIFF(d,B.DateCreated,B.DateCompleted)  AS Days ...
SELECT DATEDIFF(hh,B.DateCreated,B.DateCompleted)  AS Hours ...
SELECT DATEDIFF(mi,B.DateCreated,B.DateCompleted)  AS  Minutes ...
SELECT DATEDIFF(s,B.DateCreated,B.DateCompleted)  as seconds ...
0

Author Commented:
I need to exact difference between two dates ,
for Eg :
Date1 01:01:2008 10:00:00
Date2 03:01:2008  09:58:00

01:00:00 23:58:00
0

Commented:
if the difference is negative then how do u want the answer, pls clarify by giving an example and
what do u mean by date difference is null, both the dates are equal? if yes, then i think ur solution is right, giving the correct result.
0

Commented:
your query is right , to avoid negative value use ABS function)
see the sample funtion
``````DECLARE @Date1 AS DATETIME
SET @Date1 =  '01/01/2008 01:00:00'

DECLARE @Date2 AS DATETIME
SET @Date2 = '01/03/2008 09:58:10'

select  [dbo].[GetFormattedTime](datediff(ss,convert(datetime,@Date1,101),convert(datetime,@Date2,101)))

-----------------------------------------------------------

CREATE FUNCTION [dbo].[GetFormattedTime] (@Duration INT)
RETURNS VARCHAR (30) AS
BEGIN

DECLARE @_sec INT;
DECLARE @_min INT;
DECLARE @_hr INT;
DECLARE @_day int;
DECLARE @_duration VARCHAR(30);
DECLARE @_hour VARCHAR(10);
DECLARE @_minute VARCHAR(10);

SET @_day = ABS((@Duration/3600)/24)
SET @_hr =  ABS(@Duration/3600) - (@_day*24);
SET @_min = ABS(@Duration%3600)/60;
SET @_sec = ABS(@Duration%3600)%60;

SET @_duration = REPLICATE('0',2-LEN(@_day)) + CONVERT(VARCHAR,@_day) + ':' + REPLICATE('0',2-LEN(@_hr)) + CONVERT(VARCHAR,@_hr) +':' + REPLICATE('0',2-LEN(@_min)) + CONVERT(VARCHAR,@_min)+':' + REPLICATE('0',2-LEN(@_sec)) + CONVERT(varchar,@_sec)

RETURN @_duration;

END
``````
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.