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

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
Asked:
byte1
  • 2
1 Solution
 
BinuthCommented:
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
 
byte1Author 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
 
snehsoniCommented:
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
 
BinuthCommented:
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

Open in new window

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.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

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