Datediff - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Datediff

Author

Topic

AFK
Starting Member

26 Posts

Posted - 2005-02-11 : 16:51:31

I have this query to get date difference in hours

DATEDIFF(hh, OneDate,(Convert(smalldatetime,(Left(Convert(Char(19),Isnull(TwoDAte,Threedate),120),10) + Space(1) + Convert(Char(8),Isnull(TwoTime,'00:00'),108)),120)))

How can I modify this to give me miutes too..

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-02-11 : 17:02:39

you have three dates, and you want the difference in minutes:


Declare @date1 datetime,
	@date2 datetime,
	@date3 datetime

Set @date1 = dateadd(n,-476,getdate())
Set @date2 = getDate()
Select @date1, @date2, Left(convert(varchar,dateadd(mi,datediff(n,@date1,isnull(@date2,@date3)),0),108),5)

Corey

"If the only tool you have is a hammer, the whole world looks like a nail." - Mark Twain

AFK
Starting Member

26 Posts

Posted - 2005-02-11 : 17:27:05

I want the difference both in Hours and minutes?

AFK
Starting Member

26 Posts

Posted - 2005-02-11 : 17:36:07

Here is my declaration,

Declare date1 smalldatetime,
Declare date2 smalldatetime,
Declare date3 smalldatetime

DATEDIFF(hh, date1 ,
(Convert(smalldatetime,
(Left(Convert(Char(19),Isnull(date2 ,date3 ),120),10)
+ Space(1) + Convert(Char(8),Isnull(date4 ,'00:00'),108)),120)))

I am concatinating date2 with date4 and date4 is actually time .

so its like

date2 = 2005-01-28 00:00:00.000
date4 = 13:54:23

and when I join them I will get

2005-01-28 13:54:23.000

and I want to get difference in both hours and minutes..Is it possible because datediff will give you difference in hours or minutes?>

MichaelP
Jedi Yak

2489 Posts

Posted - 2005-02-11 : 18:10:28

Not really, but here's a solution.
You'll need to edit this a bit to get what you want, but this should give you the answer.


DECLARE @DateToCompare DATETIME

SELECT @DateToCompare = '2005-01-11 13:10'

select (CAST(DateDiff(ss, @DateToCompare, GETDATE()) /86400 as varchar) + ' days ' + 
	CAST((DateDiff(ss, @DateToCompare, GETDATE())%86400)/3600 as varchar) + ' hours ' + 
	CAST((DateDiff(ss, @DateToCompare, GETDATE())%3600)/60 as varchar) + ' minutes ' + 
	CAST(DateDiff(ss, @DateToCompare, GETDATE())%60 as varchar) + ' seconds') AS LoginDelta

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-02-11 : 19:01:38

My response gives you hours and minutes... did you run it?

Corey

"If the only tool you have is a hammer, the whole world looks like a nail." - Mark Twain

AFK
Starting Member

26 Posts

Posted - 2005-02-14 : 09:55:54

When I run this it gives me error message that incorrect syntex near cast.The thing is that I have to use 4 different dates and some of them may be null.

Date1 = 2005-01-03 10:15:00
Date2 = 2005-01-03 00:00:00 (there is no time stamp on this)
Date3 = 2005-01-03 00:00:00 (there is no time stamp on this)
Date4 = 00:00:00 (its only time)

Select Order,
(CAST(DateDiff(ss, Date1, Isnull(Date2,Date3)+ Space(1) + Convert(Char(8),Isnull(Date4,'00:00')) /86400 as varchar) + 'D'
+ CAST((DateDiff(ss, Date1, Isnull(Date2,Date3)+ Space(1) + Convert(Char(8),Isnull(Date4,'00:00'))%86400)/3600 as varchar) + 'H'
+ CAST((DateDiff(ss, Date1, Isnull(Date2,Date3)+ Space(1) + Convert(Char(8),Isnull(Date4,'00:00'))%3600)/60 as varchar) + 'M' +
CAST(DateDiff(ss, Date1, Isnull(Date2,Date3)+ Space(1) + Convert(Char(8),Isnull(Date4,'00:00'))%60 as varchar) + 'S') AS LoginDelta
From T1,
T2
where T1.Order = T2.order

Please help...

AFK
Starting Member

26 Posts

Posted - 2005-02-14 : 10:30:43

What is the difference between datetime and smalldatetime. I have declared mine as smalldatetime?

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-02-14 : 10:33:15

AFK,

Try Books Online....datetime is 8 bytes. Smalldatetime is 4.

Semper fi, Xerxes, USMC(Ret.)
-------------------------------------------------------------------------
Once a Marine Programmer Analyst ALWAYS a Marine Programmer Analyst

AFK
Starting Member

26 Posts

Posted - 2005-02-15 : 09:23:44

Michael

My report worked. It gave me days,hours,min,and sec..

You are the great..

Sorry that I was a little in hurry to get the results..Thats why when it didn't work i thought may be the query was missing something..

Great Thanks..

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources