time difference - 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)

time difference

Author

Topic

skillile
Posting Yak Master

208 Posts

Posted - 2002-05-10 : 13:10:18

This has got to be easier then what I am doing.

I want the difference from @start and @end in min:sec format.

ie it took 1:34 minutes to run this:

here is what I have:

declare @s datetime
declare @e datetime
declare @min int
declare @mina decimal(4,4)

set @s = getdate()
set @e = dateadd(ss,11,getdate())

set @min=(datediff(ss,@s, @e))

set @mina= @min/60

any help would be appreciated
thanks

slow down to move faster...

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-05-10 : 13:22:02

SELECT DateDiff(ss, @s, @e)/60 AS Minutes, ':',
DateDiff(ss, @s, @e)%60 AS Seconds

That's kinda kludgy, it would give you three columns. The combined version would be:

SELECT Cast(DateDiff(ss, @s, @e)/60 AS varchar) +
':' + Replace(Str(DateDiff(ss, @s, @e)%60, 2, 0), ' ', '0') AS Elapsed

If you want leading zeros for the minutes portion, you should use the Replace/Str formula instead of Cast.

Kevin Snow
Posting Yak Master

149 Posts

Posted - 2002-05-10 : 13:35:41

I tried your code. There is no way it should take 1:34 minutes to run. I wrote the following equivalent, but I don't see any advantage to it.

declare @s float
declare @e float
declare @min float
declare @mina decimal(4,4)

set @s = cast(getdate() as float)
set @e = cast(dateadd(ss,11,getdate()) as float)

set @min= @e-@s

Select @min

Select @mina= @min*24*60 --fraction of a day * hours * minutes
Select @mina

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources