Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Difference between two dates

Posted on 2008-09-30
4
Medium Priority
?
340 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 2000 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

Tech or Treat! - Giveaway

Submit an article about your scariest tech experience—and the solution—and you’ll be automatically entered to win one of 4 fantastic tech gadgets.

Question has a verified solution.

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

by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…

609 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