Solved

Difference between two dates

Posted on 2008-09-30
4
334 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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Suggested Solutions

Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
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.
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

737 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