March 16, 2009 at 7:28 am
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
March 16, 2009 at 7:46 am
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)
March 16, 2009 at 8:36 am
Perfect! Thanks!!
August 18, 2009 at 8:04 am
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.
August 18, 2009 at 9:02 am
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]
August 18, 2009 at 9:45 am
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
) aResults:
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)
August 19, 2009 at 8:20 am
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 ONGO
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[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"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."
August 19, 2009 at 8:44 am
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 ONGO
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.
August 19, 2009 at 11:10 am
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...
August 21, 2012 at 12:22 pm
[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.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply