Formating the result of DateDiff

Question

Formating the result of DateDiff

CanuckBuck

Hall of Fame

Points: 3915
More actions
May 28, 2008 at 1:25 pm

#70081

This should be simple but I just can't figure it out.
I want to display the elapsed time of length of a query as hh:mm:ss
This is what I have;
DECLARE @Time DateTime
SET @time = GETDATE()
[MY QUERY]
PRINT DATEDIFF(ms,@time, GETDATE())
This of course displays an integer value in milliseconds. I'd be much happier knowing how many mm:ss the query ran.
How do I make that happen?
Thanks in advance to any and all who can help me with this.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736557 More actions · Answer 1

you need to do some math to get the results.

You can divide by 1000 to get seconds, then you'd have to divide by 60 to get minutes, and subtract those minutes from the total seconds (or use modulo) to get leftover seconds.

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 2

or - you add it back into a datetime. Then use whatever date/time format suits you.

DECLARE @Time DateTime

SET @time = GETDATE()

[MY QUERY]

PRINT dateadd(ms,DATEDIFF(ms,@time, GETDATE()),0)

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

CanuckBuck Hall of Fame Points: 3915 More actions · Answer 3

Thank you both Steve and Matt.

Steve;

While you were replying I was doing research and came across the date math solution that you mentioned at

http://www.sqlteam.com/article/working-with-time-spans-and-durations-in-sql-server

It wound up looking like this;

PRINT REPLICATE(0,2-LEN(CAST(DATEDIFF(s,@BeginTime,@EndTime) % 3600/60 AS VARCHAR(2)))) + CAST(DATEDIFF(s,@BeginTime,@EndTime) % 3600/60 AS VARCHAR(2)) + ':' + REPLICATE(0,2-LEN(CAST(DATEDIFF(s,@BeginTime,@EndTime) % 60 AS VARCHAR(2)))) + CAST(DATEDIFF(s,@BeginTime,@EndTime) % 60 AS VARCHAR(2))

Yikes!

Matt;

I tried your idea. It looks like this;

PRINT Convert(varchar(8), dateadd(ms,DATEDIFF(ms,@BeginTime, GETDATE()),0), 8)

This is exactly what I was looking for.

Turns out that both methods give exactly the same result. I like the shorter one cause I'm lazy 😀

Sergiy SSC Guru Points: 110208 More actions · Answer 4

I prefer much more simple code:

PRINT Convert(varchar(12), GETDATE() - @BeginTime, 114)

_____________
Code for TallyGenerator

CanuckBuck Hall of Fame Points: 3915 More actions · Answer 5

And that's even cooler yet since it gives me milliseconds as well. I like it! Thank you.