InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSun, 26 May 2013 00:16:07 GMT20http://www.sqlservercentral.com/Forums/Topic676470-338-1.aspx[font="Courier New"][/font]DECLARE @BatchStart datetime = GETDATE(), @StageStart datetime, @Iteration int = 0, @Iterations int = 5PRINT '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)ENDPRINT 'Batch time : ' + CONVERT(varchar(12), DATEADD(ms, DATEDIFF(ms, @BatchStart, GETDATE()), 0), 114)Good for 24h, but can easily be adjusted for more.Tue, 21 Aug 2012 12:22:11 GMTmacintyre_berniehttp://www.sqlservercentral.com/Forums/Topic676470-338-1.aspx[quote] 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. [/quote]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...Wed, 19 Aug 2009 11:10:26 GMTUKGavhttp://www.sqlservercentral.com/Forums/Topic676470-338-1.aspx[quote][b]jcrawf02 (8/19/2009)[/b][hr][quote][b]Gav B (8/18/2009)[/b][hr]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?[code] SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE 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 INTSET @I = DATEDIFF(ms,GETDATE()-RAND()*24,GETDATE())SELECTconvert(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][/code]- Gav B.[/quote]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.[/quote]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.Wed, 19 Aug 2009 08:44:37 GMTMichael Valentine Joneshttp://www.sqlservercentral.com/Forums/Topic676470-338-1.aspx[quote][b]Gav B (8/18/2009)[/b][hr]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?[code] SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE 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 INTSET @I = DATEDIFF(ms,GETDATE()-RAND()*24,GETDATE())SELECTconvert(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][/code]- Gav B.[/quote]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.Wed, 19 Aug 2009 08:20:48 GMTjcrawf02http://www.sqlservercentral.com/Forums/Topic676470-338-1.aspxThis is a little simpler and will work with any date range.[code="sql"]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[/code]Results:[code="sql"]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)[/code]Tue, 18 Aug 2009 09:45:53 GMTMichael Valentine Joneshttp://www.sqlservercentral.com/Forums/Topic676470-338-1.aspxIf 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: [code="sql"]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][/code]Tue, 18 Aug 2009 09:02:33 GMTandrewd.smithhttp://www.sqlservercentral.com/Forums/Topic676470-338-1.aspxI 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?[code] SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE 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 INTSET @I = DATEDIFF(ms,GETDATE()-RAND()*24,GETDATE())SELECTconvert(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][/code]- Gav B.Tue, 18 Aug 2009 08:04:44 GMTUKGavhttp://www.sqlservercentral.com/Forums/Topic676470-338-1.aspxPerfect! Thanks!!Mon, 16 Mar 2009 08:36:27 GMTMichael Espositohttp://www.sqlservercentral.com/Forums/Topic676470-338-1.aspxThis 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). [code]DECLARE @dt1 datetimeDECLARE @dt2 datetimeSELECT @dt1 = '20090316 12:00:00.000', @dt2 = GETDATE()SELECT CONVERT(varchar(12), DATEADD(ms, DATEDIFF(ms, @dt1, @dt2), 0), 114)[/code]Mon, 16 Mar 2009 07:46:52 GMTandrewd.smithhttp://www.sqlservercentral.com/Forums/Topic676470-338-1.aspxI have the following T-SQL code to change the difference between datetime columns into hours, minutes, and secondsCONVERT(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!MichaelMon, 16 Mar 2009 07:28:15 GMTMichael Esposito