Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Convert DateDiff into Hours, Minutes, Seconds, Milliseconds Expand / Collapse
Author
Message
Posted Monday, March 16, 2009 7:28 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, May 1, 2009 1:36 PM
Points: 136, 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
Post #676470
Posted Monday, March 16, 2009 7:46 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, October 24, 2012 2:12 PM
Points: 1,213, Visits: 3,232
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)

Post #676502
Posted Monday, March 16, 2009 8:36 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, May 1, 2009 1:36 PM
Points: 136, Visits: 59
Perfect! Thanks!!
Post #676544
Posted Tuesday, August 18, 2009 8:04 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Today @ 6:21 AM
Points: 34, Visits: 177
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.
Post #772735
Posted Tuesday, August 18, 2009 9:02 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, October 24, 2012 2:12 PM
Points: 1,213, Visits: 3,232
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]

Post #772797
Posted Tuesday, August 18, 2009 9:45 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 12:39 PM
Points: 3,122, Visits: 11,405
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)

Post #772846
Posted Wednesday, August 19, 2009 8:20 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 8:05 AM
Points: 2,658, Visits: 19,191
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."
Post #773561
Posted Wednesday, August 19, 2009 8:44 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 12:39 PM
Points: 3,122, Visits: 11,405
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.



Post #773585
Posted Wednesday, August 19, 2009 11:10 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Today @ 6:21 AM
Points: 34, Visits: 177
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...
Post #773753
Posted Tuesday, August 21, 2012 12:22 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 12:51 PM
Points: 2, Visits: 393
[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.
Post #1348017
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse