Solved

Difference between two dates

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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
create index to c1, c2 and c3 9 65
sql query help 7 96
Truncate vs Delete 63 101
Need help with a query 6 67
Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Need to grow your business through quality cloud solutions? With everything required to build a cloud platform and solution, you may feel like the distance between you and the cloud is quite long. Help is here. Spend some time learning about the Con…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

919 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now