Solved

Difference between two dates

Posted on 2008-09-30
4
335 Views
Last Modified: 2008-10-16
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
Comment
Question by:byte1
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 14

Expert Comment

by:Binuth
ID: 22611943
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 Comment

by:byte1
ID: 22612039
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
 
LVL 1

Expert Comment

by:snehsoni
ID: 22612226
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
 
LVL 14

Accepted Solution

by:
Binuth earned 500 total points
ID: 22612910
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

Featured Post

What Is Transaction Monitoring and who needs it?

Synthetic Transaction Monitoring that you need for the day to day, which ensures your business website keeps running optimally, and that there is no downtime to impact your customer experience.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
In this video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

717 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question