Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Convert DateDiff into Hours, Minutes, Seconds, Milliseconds


Convert DateDiff into Hours, Minutes, Seconds, Milliseconds

Author
Message
Michael Esposito
Michael Esposito
SSC-Enthusiastic
SSC-Enthusiastic (168 reputation)SSC-Enthusiastic (168 reputation)SSC-Enthusiastic (168 reputation)SSC-Enthusiastic (168 reputation)SSC-Enthusiastic (168 reputation)SSC-Enthusiastic (168 reputation)SSC-Enthusiastic (168 reputation)SSC-Enthusiastic (168 reputation)

Group: General Forum Members
Points: 168 Visits: 59
I have the following T-SQL code to change the difference between datetime columns into hours, minutes, and seconds

CONVERT(varchar(6), DATEDIFF(second, b.DateTimeStamp, c.DateTimeStamp)/3600) + ':' + RIGHT('0' + CONVERT(varchar(2), (DATEDIFF(second, b.DateTimeStamp, c.DateTimeStamp) % 3600) / 60), 2) + ':' + RIGHT('0' + CONVERT(varchar(2), DATEDIFF(second, b.DateTimeStamp, c.DateTimeStamp) % 60), 2)

but I also wanted milliseconds. I have searched and have not found anything. Does anyone have any ideas?

Thanks!
Michael
andrewd.smith
andrewd.smith
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1290 Visits: 3232
This will return the time difference including milliseconds. It will work fine if the time difference is guaranteed to be less than 24 hours.

However, if the time difference is greater than 24 hours, it will not give you what you want as it will display the number of hours modulo 24. Also, the time difference calculation will overflow if the difference is greater than about 24.8 days (when number of milliseconds >= 2e31).

DECLARE @dt1 datetime
DECLARE @dt2 datetime
SELECT @dt1 = '20090316 12:00:00.000', @dt2 = GETDATE()

SELECT CONVERT(varchar(12), DATEADD(ms, DATEDIFF(ms, @dt1, @dt2), 0), 114)


Michael Esposito
Michael Esposito
SSC-Enthusiastic
SSC-Enthusiastic (168 reputation)SSC-Enthusiastic (168 reputation)SSC-Enthusiastic (168 reputation)SSC-Enthusiastic (168 reputation)SSC-Enthusiastic (168 reputation)SSC-Enthusiastic (168 reputation)SSC-Enthusiastic (168 reputation)SSC-Enthusiastic (168 reputation)

Group: General Forum Members
Points: 168 Visits: 59
Perfect! Thanks!!
UKGav
UKGav
Valued Member
Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)

Group: General Forum Members
Points: 54 Visits: 232
I found a script on the net and altered it for my needs.
I'd like to know what others think of this and if you can envisage any problems with it?

 SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[zTest_SP_DATEDIFF_TIMER] AS

-- CREATED: 18/08/2009 by Gav B.
-- DESCRIPTION: This procedure calculates the difference between 2 dates right down to the millisecond (0.003sec accuracy in SQL2005, untested in 2008)
--
-- NOTES:
-- @I_OverFlows = DATEDIFF(ms,GETDATE()-24.49,GETDATE())
-- This represents the maximum of approx 24 Days 11 Hours 50 Mins before causing an "overflow at runtime".
-- RAND function used below just to give a random number for demonstration purposes.
-- Unknown at time of writting WHY the combination of % and / works, but it seems to quite well...

DECLARE @I INT
SET @I = DATEDIFF(ms,GETDATE()-RAND()*24,GETDATE())

SELECT
convert(varchar(10), (@I/86400000)) + ' Days ' +
convert(varchar(10), ((@I%86400000)/3600000)) + ' Hours '+
convert(varchar(10), (((@I%86400000)%3600000)/60000)) + ' Mins '+
convert(varchar(10), ((((@I%86400000)%3600000)%60000)/1000)) + ' sec ' +
convert(varchar(10), (((@I%86400000)%3600000)%1000)) + ' ms ' AS [DD:HH:MM:SS:MS]



- Gav B.
andrewd.smith
andrewd.smith
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1290 Visits: 3232
If you must produce formatted output directly from SQL rather than in the front end then your approach is reasonable, however, there are some unecessary modulo operations in your expressions, e.g. ((@I%86400000)%3600000)%60000 is equivalent to @I%60000, so your statement can be rewritten as:

SELECT
convert(varchar(10), @I/86400000) + ' Days ' +
convert(varchar(10), (@I%86400000)/3600000) + ' Hours '+
convert(varchar(10), (@I%3600000)/60000) + ' Mins '+
convert(varchar(10), (@I%60000)/1000) + ' sec ' +
convert(varchar(10), @I%1000) + ' ms ' AS [DD:HH:MM:SS:MS]


Michael Valentine Jones
Michael Valentine Jones
Hall of Fame
Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)

Group: General Forum Members
Points: 3826 Visits: 11771
This is a little simpler and will work with any date range.

select
*,
Days = datediff(dd,0,DateDif),
Hours = datepart(hour,DateDif),
Minutes = datepart(minute,DateDif),
Seconds = datepart(second,DateDif),
MS = datepart(ms,DateDif)
from
(
select
DateDif = EndDate-StartDate,
aa.*
from
( -- Test Data
Select
StartDate = convert(datetime,'20090213 02:44:37.923'),
EndDate = convert(datetime,'20090715 13:24:45.837')
) aa
) a

Results:
DateDif                  StartDate                EndDate                 Days Hours Minutes Seconds MS
----------------------- ----------------------- ----------------------- ---- ----- ------- ------- ---
1900-06-02 10:40:07.913 2009-02-13 02:44:37.923 2009-07-15 13:24:45.837 152 10 40 7 913

(1 row(s) affected)


jcrawf02
jcrawf02
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1576 Visits: 19324
Gav B (8/18/2009)
I found a script on the net and altered it for my needs.
I'd like to know what others think of this and if you can envisage any problems with it?

 SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[zTest_SP_DATEDIFF_TIMER] AS

-- CREATED: 18/08/2009 by Gav B.
-- DESCRIPTION: This procedure calculates the difference between 2 dates right down to the millisecond (0.003sec accuracy in SQL2005, untested in 2008)
--
-- NOTES:
-- @I_OverFlows = DATEDIFF(ms,GETDATE()-24.49,GETDATE())
-- This represents the maximum of approx 24 Days 11 Hours 50 Mins before causing an "overflow at runtime".
-- RAND function used below just to give a random number for demonstration purposes.
-- Unknown at time of writting WHY the combination of % and / works, but it seems to quite well...

DECLARE @I INT
SET @I = DATEDIFF(ms,GETDATE()-RAND()*24,GETDATE())

SELECT
convert(varchar(10), (@I/86400000)) + ' Days ' +
convert(varchar(10), ((@I%86400000)/3600000)) + ' Hours '+
convert(varchar(10), (((@I%86400000)%3600000)/60000)) + ' Mins '+
convert(varchar(10), ((((@I%86400000)%3600000)%60000)/1000)) + ' sec ' +
convert(varchar(10), (((@I%86400000)%3600000)%1000)) + ' ms ' AS [DD:HH:MM:SS:MS]



- Gav B.

I can envisage a problem. You didn't credit your source for the script that you "found on the net". Granted, you altered it, but alteration is not authoring, especially since your alteration may have been a minor detail, we have no way of knowing.

---------------------------------------------------------
How best to post your question
How to post performance problems
Tally Table:What it is and how it replaces a loop

"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
Michael Valentine Jones
Michael Valentine Jones
Hall of Fame
Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)

Group: General Forum Members
Points: 3826 Visits: 11771
jcrawf02 (8/19/2009)
Gav B (8/18/2009)
I found a script on the net and altered it for my needs.
I'd like to know what others think of this and if you can envisage any problems with it?

 SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[zTest_SP_DATEDIFF_TIMER] AS

-- CREATED: 18/08/2009 by Gav B.
-- DESCRIPTION: This procedure calculates the difference between 2 dates right down to the millisecond (0.003sec accuracy in SQL2005, untested in 2008)
--
-- NOTES:
-- @I_OverFlows = DATEDIFF(ms,GETDATE()-24.49,GETDATE())
-- This represents the maximum of approx 24 Days 11 Hours 50 Mins before causing an "overflow at runtime".
-- RAND function used below just to give a random number for demonstration purposes.
-- Unknown at time of writting WHY the combination of % and / works, but it seems to quite well...

DECLARE @I INT
SET @I = DATEDIFF(ms,GETDATE()-RAND()*24,GETDATE())

SELECT
convert(varchar(10), (@I/86400000)) + ' Days ' +
convert(varchar(10), ((@I%86400000)/3600000)) + ' Hours '+
convert(varchar(10), (((@I%86400000)%3600000)/60000)) + ' Mins '+
convert(varchar(10), ((((@I%86400000)%3600000)%60000)/1000)) + ' sec ' +
convert(varchar(10), (((@I%86400000)%3600000)%1000)) + ' ms ' AS [DD:HH:MM:SS:MS]



- Gav B.

I can envisage a problem. You didn't credit your source for the script that you "found on the net". Granted, you altered it, but alteration is not authoring, especially since your alteration may have been a minor detail, we have no way of knowing.



The solution has a fairly serious limitation in only working over a fairly short of time, less than 25 days, so maybe it's better he didn't give credit.
UKGav
UKGav
Valued Member
Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)

Group: General Forum Members
Points: 54 Visits: 232
The solution has a fairly serious limitation in only working over a fairly short of time, less than 25 days, so maybe it's better he didn't give credit.


Thank you for script and yes thats quite right, my altertered version of the original was quite limited.

The original was only to the second not millisecond and as such worked just fine.
I was just pointing out that I haven't written the whole thing from scratch.

Next I need to wrap it all up into a SP or Function... hmm...
macintyre_bernie
macintyre_bernie
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 Visits: 446
[font="Courier New"][/font]DECLARE @BatchStart datetime = GETDATE(), @StageStart datetime, @Iteration int = 0, @Iterations int = 5

PRINT 'Batch start: ' + CONVERT(varchar(12), DATEADD(ms, DATEDIFF(ms, @BatchStart, @BatchStart), 0), 114)
WHILE @Iteration <> @Iterations BEGIN
SELECT @Iteration = @Iteration + 1, @StageStart = GETDATE()
WAITFOR DELAY '00:00:01'
PRINT ' Iteration ' + CAST(@Iteration as varchar(3)) + ': ' + CONVERT(varchar(12), DATEADD(ms, DATEDIFF(ms, @StageStart, GETDATE()), 0), 114)
END
PRINT 'Batch time : ' + CONVERT(varchar(12), DATEADD(ms, DATEDIFF(ms, @BatchStart, GETDATE()), 0), 114)

Good for 24h, but can easily be adjusted for more.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search