"CountDown" Sytle 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

Transact-SQL (2000)

"CountDown" Sytle DateDiff

Author

Topic

MichaelP
Jedi Yak

2489 Posts

Posted - 2002-05-29 : 17:32:29

I'd like to show the datediff of two dates, but instead of getting just one datepart, I'd like all dateparts.

Given
StartDate:5/20/2002 12:11:33PM
EndDate:5/29/2002 10:34:58AM

I want to output:
8 days, 10 hours, 35 minutes

I know DateDiff will give me ONE of those units....
I think what I haev to do is do DATEDIFF(ss, startdate, endate) and then calculate how many days, hours, minutes, and seconds are in that rather large numebr of seconds.

Any ideas? Thanks All
Michael

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-05-29 : 18:04:40

I got this to work:

DECLARE @diff int
SELECT @diff=DateDiff(ss,'5/20/2002 12:11:33PM','5/29/2002 10:34:58AM')
SELECT CAST(@diff/86400 as varchar) + ' days ' +
CAST((@diff%86400)/3600 as varchar) + ' hours ' +
CAST((@diff%3600)/60 as varchar) + ' minutes ' +
CAST(@diff%60 as varchar) + ' seconds'

Each formula is calculated based on the number of seconds in a day, hour, minute, etc. and takes the appropriate remainder. If you can't use a variable just substitute the DateDiff expression wherever the @diff variable appears.

MichaelP
Jedi Yak

2489 Posts

Posted - 2002-05-29 : 18:09:28

Rob you are the man! I'll need to adapt it a bit for what I'm doing, but I think I can do that.

Thanks again for your good and very fast help!

Michael

Kevin Snow
Posting Yak Master

149 Posts

Posted - 2002-05-29 : 18:14:29

How about something like this?

declare @dateA datetime, @dateB datetime,@dateC datetime
set @dateB='5/25/2002 10:42:00'
set @dateA='5/13/2002'
set @dateC=@dateB-@dateA
Select cast(datepart(d,@dateC)as varchar(3)) +
' days ' +
cast(datepart(hh,@dateC) as varchar(2))+
' hours ' +
cast(datepart(mi,@dateC) as varchar(2))+
' minutes'

yakoo
Constraint Violating Yak Guru

312 Posts

Posted - 2002-05-29 : 18:14:56

I got snipped but here was what I had



DECLARE @StartDate as datetime, @EndDate as datetime

SELECT @StartDate = '05/20/2002 12:11:33PM', @EndDate = '05/29/2002 10:34:58AM'

SELECT
cast(
	(
		DATEDIFF(ss, @StartDate, @EndDate) / (24 * 60 * 60)
	) as varchar
) + ' days, ' +
cast(
	(
		(
			DATEDIFF(ss, @StartDate, @EndDate) % (24 * 60 * 60)
		) / (60 * 60)
	) as varchar
) + ' hours, ' +
cast(
	(
		(
			(
				DATEDIFF(ss, @StartDate, @EndDate) % (24 * 60 * 60)
			) % (60 * 60)
		) / (60)
	) as varchar
) + ' minutes, ' +
cast(
	(
		(
			(
				(
					DATEDIFF(ss, @StartDate, @EndDate) % (24 * 60 * 60)
				) % (60 * 60)
			) % (60)
		) 
	) as varchar
) + ' seconds'

Got SQL?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources