September 5, 2012 at 3:54 pm
Oops, the s_difference+1 is incorrect. That should be been just s_difference. The minus is correct.
September 5, 2012 at 3:54 pm
I saw that you had made an extremely compact version. Haven't had time to check it out yet, but it looks pretty good at a glance.
Thanks for all the help...
Bruce
September 6, 2012 at 2:18 am
bmahf (9/5/2012)
Oops, the s_difference+1 is incorrect. That should be been just s_difference. The minus is correct.
Here's a version which corrects for aTime being more recent than bTime, and includes a filter designed to return rows where the algorithm falls over. I've set it up with multiple CROSS APPLY operators as before, because a cascaded CROSS APPLY like this is much easier to understand and debug. Once the code is verified, the cCA can be "compacted down".
WITH MyCTE (aTime, bTime)
AS
(
SELECT CONVERT(datetime2,'1900-01-01 00:08:00.0000001'), CONVERT(datetime2,'1900-01-01 00:09:18.361')
UNION ALL SELECT '1900-01-01 00:08:00.0000001', '1900-01-01 00:13:50.705'
UNION ALL SELECT '1900-01-01 00:09:18.361', '1900-01-01 03:13:50.7050001'
UNION ALL SELECT '1900-01-01 23:09:18.361', '1900-01-02 02:13:50.7050001'
UNION ALL SELECT '1900-01-01 00:00:00.000000111', '1900-01-01 00:00:00.000000222'
UNION ALL SELECT '1900-01-01 00:10:00.000000111', '1900-01-01 00:00:00.000000222'
)
SELECT
*
From myCTE a1
CROSS APPLY (SELECT SecondsDifferenceINT = DATEDIFF(second,aTime, bTime)) a
CROSS APPLY (SELECT AdjustedFirstDateDT = DATEADD(second,SecondsDifferenceINT,aTime)) b
CROSS APPLY (SELECT NanosecondRemainderINT = DATEDIFF(ns,AdjustedFirstDateDT,bTime)) c
-- use ABS() to convert seconds difference to a positive number
-- where aTime is more recent than bTime
CROSS APPLY (SELECT TimeToSecondsDT = DATEADD(second,ABS(SecondsDifferenceINT),0)) d
CROSS APPLY (SELECT TimeToSecondsVC = CONVERT(VARCHAR(8),TimeToSecondsDT,108)) e
CROSS APPLY (SELECT ResultVC = e.TimeToSecondsVC + '.'
+ RIGHT('000000000'+CAST(NanosecondRemainderINT AS VARCHAR),9)
) f
CROSS APPLY (
SELECT
TimeResult= CAST(f.ResultVC AS TIME(7)),
SimpleProof = CAST(DATEADD(ms,ABS(DATEDIFF(ms,aTime, bTime)),CAST(CAST(0 AS DATETIME) AS DATETIME2)) AS TIME(3))
) g
-- Filter for exceptions - this will return rows where the algorithm fails
-- (if SQL Server doesn't bork first).
WHERE CAST(TimeResult AS DATETIME) <> CAST(SimpleProof AS DATETIME)
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
September 6, 2012 at 3:31 am
This looks like a thread without a one-line SQLCLR solution yet :w00t:
-- see later post for updated code --
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
September 6, 2012 at 4:05 am
Nice one, Paul.
Here are two different one-line TSQL solutions:
;With MyCTE (aTime, bTime)
AS
(
SELECT CONVERT(datetime2,'1900-01-01 00:08:00.0000001'), CONVERT(datetime2,'1900-01-01 00:09:18.361')
UNION ALL SELECT '1900-01-01 00:08:00.0000001', '1900-01-01 00:13:50.705'
UNION ALL SELECT '1900-01-01 00:09:18.361', '1900-01-01 03:13:50.7050001'
UNION ALL SELECT '1900-01-01 23:09:18.361', '1900-01-02 02:13:50.7050001'
UNION ALL SELECT '1900-01-01 00:00:00.000000111', '1900-01-01 00:00:00.000000222'
UNION ALL SELECT '1900-01-01 00:10:00.000000111', '1900-01-01 00:00:00.000000222'
)
SELECT
aTime,
bTime,
SimpleProof = CAST(DATEADD(ms,ABS(DATEDIFF(ms,aTime, bTime)),CAST(CAST(0 AS DATETIME) AS DATETIME2)) AS TIME(3)),
-- Method 1 (Chris R - too shy to post)
TimeResult1= CAST(
DATEADD(
NS,
ABS(CAST(SUBSTRING(CAST(btime as varchar(30)),20,8) AS float) -
CAST(SUBSTRING(CAST(atime as varchar(30)),20,8) AS float)
) *1000000000,
DATEADD(
SS,
ABS(DATEDIFF(SS, aTime, btime)),
CAST('1900-01-01 00:00' AS datetime2))
)
AS time(7)),
-- Method 2 (Chris M)
TimeResult2= CAST(
CONVERT(VARCHAR(8),
DATEADD(second,ABS(DATEDIFF(second,aTime, bTime)),0),108) + '.'
+ RIGHT('000000000'
+ CAST(ABS(
DATEDIFF(ns,
DATEADD(second,DATEDIFF(second,aTime, bTime),aTime),
bTime))
AS VARCHAR)
,9)
AS TIME(7))
From myCTE
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
September 6, 2012 at 4:53 am
One more:
TimeResult3= CAST(
DATEADD(ns,ABS(DATEPART(ns,btime)-DATEPART(ns,atime)),
DATEADD(ss,ABS(DATEDIFF(ss, aTime, btime)),CAST('1900-01-01 00:00' AS datetime2)))
AS TIME(7))
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
September 6, 2012 at 8:50 am
ChrisM@Work (9/6/2012)
One more:
TimeResult3= CAST(
DATEADD(ns,ABS(DATEPART(ns,btime)-DATEPART(ns,atime)),
DATEADD(ss,ABS(DATEDIFF(ss, aTime, btime)),CAST('1900-01-01 00:00' AS datetime2)))
AS TIME(7))
Aarrgghh!! They all fall over given suitable edge cases in the data.
DATEDIFF counts boundaries - if there's a few nanoseconds difference between two dates but a seconds boundary is crossed, DATEDIFF(second... returns 1 second. So, revising the above code, and including Scott's (which doesn't have this problem but fails with startdate more recent than enddate as he pointed out);
;With MyCTE (ID, aTime, bTime)
AS
(
SELECT 1, CONVERT(datetime2,'1900-01-01 00:08:00.0000001'), CONVERT(datetime2,'1900-01-01 00:09:18.361')
UNION ALL SELECT 2, '1900-01-01 00:08:00.0000001', '1900-01-01 00:13:50.705'
UNION ALL SELECT 3, '1900-01-01 00:09:18.361', '1900-01-01 03:13:50.7050001'
UNION ALL SELECT 4, '1900-01-01 23:09:18.361', '1900-01-02 02:13:50.7050001'
UNION ALL SELECT 5, '1900-01-01 00:00:00.0000001', '1900-01-01 00:00:00.0000002'
UNION ALL SELECT 6, '1900-01-01 00:00:00.0000002', '1900-01-01 00:00:00.0000001'
UNION ALL SELECT 7, '1900-01-01 00:10:00.0000001', '1900-01-01 00:00:00.0000002'
UNION ALL SELECT 8, '1900-01-01 00:10:01.9999988', '1900-01-01 00:10:00.0000022'
UNION ALL SELECT 9, '1900-01-01 00:10:00.9999998', '1900-01-01 00:10:01.0000001'
UNION ALL SELECT 10, '1900-01-01 00:10:00.0000023', '1900-01-01 00:10:01.0000022'
UNION ALL SELECT 11, '1900-01-01 00:10:00.0000022', '1900-01-01 00:10:01.0000022'
UNION ALL SELECT 12, '1900-01-01 00:10:00.0000021', '1900-01-01 00:10:01.0000022'
)
SELECT
aTime,
bTime,
SimpleProof = CAST(DATEADD(ms,ABS(DATEDIFF(ms,aTime, bTime)),CAST(CAST(0 AS DATETIME) AS DATETIME2)) AS TIME(3)),
TimeResult1 = -- ChrisM
CAST(DATEADD(ns,
(CASE WHEN aTime < bTime
THEN DATEPART(ns,bTime)-DATEPART(ns,aTime)
ELSE DATEPART(ns,aTime)-DATEPART(ns,bTime) END),
DATEADD(ss,
ABS(DATEDIFF(ss,aTime,bTime)),
CAST('1900-01-01 00:00' AS datetime2))
) AS TIME(7)),
TimeResult2 = -- Tardy ChrisR
CAST(DATEADD(nanosecond,
CASE
WHEN Sec = 0 THEN ABS(NanoSec)
WHEN Sec < 0 THEN NanoSec * -1
ELSE NanoSec
END,
DATEADD(second,
ABS(Sec),
CAST('1900-01-01 00:00' AS datetime2))
) AS TIME(7)),
TimeDiff = -- ScottP
CONVERT(varchar(8), DATEADD(SECOND, DATEDIFF(SECOND, StartTime, EndTime)
- CASE WHEN DATEPART(NANOSECOND, StartTime) > DATEPART(NANOSECOND, EndTime) THEN 1 ELSE 0 END, 0), 108) +
'.' + RIGHT(REPLICATE('0', 7) +
CAST(((CASE WHEN DATEPART(NANOSECOND, StartTime) > DATEPART(NANOSECOND, EndTime) THEN 1000000000 ELSE 0 END +
DATEPART(NANOSECOND, EndTime) - DATEPART(NANOSECOND, StartTime)) / 100) AS varchar(7)), 7)
From MyCTE
CROSS APPLY (
SELECT
Sec = (DATEDIFF(second, aTime, btime)),
NanoSec = (DATEPART(ns,btime)-DATEPART(ns,atime)),
StartTime = CASE WHEN aTime < bTime THEN aTime ELSE bTime END,
EndTime = CASE WHEN aTime < bTime THEN bTime ELSE aTime END
) x
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
September 6, 2012 at 12:43 pm
Try this. The issue seems to be that the DATEDIFF and DATEPART functions seem to return an INT value, which at a precision level of 7 gets overflowed. So I broke it down and convert the nanosecond parts to BIGINT and subtract to get the difference as an integer. One interesting thing that I noticed with nanoseconds is that DATEDIFF returns 7 significant digits but always puts '00' on the end so a difference of 5448951 nanoseconds actually displays as '544895100'. Perhaps in later versions MS plans to allow a higher precision level for date/time functions?
Just FYI, in the code below I added a value for days which may not be needed for your application.
DECLARE
@StartTime DATETIME2(7)
,@EndTime DATETIME2(7)
,@TimeSpan TIME
SET @StartTime = '2012-04-26 13:53:43.0000000'
SET @EndTime = '2012-04-26 13:54:44.5448951'
--SET @StartTime = '2012-10-01 12:00:00.0130952'
--SET @EndTime = '2012-10-01 17:00:04.9845083'
SELECT
@StartTime AS stime
,@EndTime AS etime
,RIGHT('00' + CONVERT(VARCHAR,DATEDIFF(day,@StartTime,@EndTime)),2)
+ ':' + RIGHT('00' + CONVERT(VARCHAR,DATEDIFF(hour,@StartTime,@EndTime) % 24),2)
+ ':' + RIGHT('00' + CONVERT(VARCHAR,DATEDIFF(minute,@StartTime,@EndTime) % 60),2)
+ ':' + RIGHT('00' + CONVERT(VARCHAR,(DATEDIFF(second,@StartTime,@EndTime)) % 60),2)
+ '.' + RIGHT('0000000' + CONVERT(VARCHAR,(CAST((CAST(CAST(DATEPART(nanosecond,@EndTime) AS BIGINT) AS DECIMAL(18,8))/100) - (CAST(CAST(DATEPART(nanosecond,@StartTime) AS BIGINT) AS DECIMAL(18,8))/100) AS BIGINT))),7) AS ndiff
September 6, 2012 at 12:59 pm
That is because the accuracy of datetime2 is only to 100 nanoseconds.
http://msdn.microsoft.com/en-us/library/bb677335(v=sql.100).aspx
September 6, 2012 at 1:09 pm
return an INT value, which at a precision level of 7 gets overflowed.
The INT can't overflow returning billionths of a second -- the max possible value is 999,999,999, which is safely within an INT value range.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
September 6, 2012 at 1:37 pm
ScottPletcher (9/6/2012)
return an INT value, which at a precision level of 7 gets overflowed.
The INT can't overflow returning billionths of a second -- the max possible value is 99,999,999, which is safely within an INT value range.
I stand corrected on the INT vs BIGINT. I tested it with INT and the solution above still works OK. So there's something else about the DATEDIFF for nanoseconds that can cause an overflow.
September 6, 2012 at 1:53 pm
Steven Willis (9/6/2012)
ScottPletcher (9/6/2012)
return an INT value, which at a precision level of 7 gets overflowed.
The INT can't overflow returning billionths of a second -- the max possible value is 99,999,999, which is safely within an INT value range.
I stand corrected on the INT vs BIGINT. I tested it with INT and the solution above still works OK. So there's something else about the DATEDIFF for nanoseconds that can cause an overflow.
The first select will work, the second will fail with an overflow:
SELECT DATEDIFF(ns,CAST('2012-09-06 13:50:00.000' AS DATETIME2),CAST('2012-09-06 13:50:02.000' AS DATETIME2))
go
SELECT DATEDIFF(ns,CAST('2012-09-06 13:50:00.000' AS DATETIME2),CAST('2012-09-06 13:50:03.000' AS DATETIME2))
go
September 6, 2012 at 2:08 pm
Lynn Pettis (9/6/2012)
Steven Willis (9/6/2012)
ScottPletcher (9/6/2012)
return an INT value, which at a precision level of 7 gets overflowed.
The INT can't overflow returning billionths of a second -- the max possible value is 99,999,999, which is safely within an INT value range.
I stand corrected on the INT vs BIGINT. I tested it with INT and the solution above still works OK. So there's something else about the DATEDIFF for nanoseconds that can cause an overflow.
The first select will work, the second will fail with an overflow:
SELECT DATEDIFF(ns,CAST('2012-09-06 13:50:00.000' AS DATETIME2),CAST('2012-09-06 13:50:02.000' AS DATETIME2))
go
SELECT DATEDIFF(ns,CAST('2012-09-06 13:50:0.000' AS DATETIME2),CAST('2012-09-06 13:50:03.000' AS DATETIME2))
go
That was dealing with DATEPART not DATEDIFF.
DATEPART(NS, ...) can never exceed 999,999,999.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
September 6, 2012 at 2:32 pm
So it turns out that each of these solutions is missing something, it's either the fraction part sometimes comes out incorrect, because it wasn't adjusted as I was doing in my case statement, or the seconds were not adjusted, which I also did in the other case statement I sent. I also found that the case where I adjusted the fraction part because it was negative still had another issue, which was that it was never adjusted for position.
So since this is for a one-time converter, which doesn't have to be optimized to the extreme, I rewrote my version, and made it into a function, and I call that function inside my select. My version now looks like this, and I haven't found any problems with it yet:
But, I must say it is interested to try to figure out the optimized version. It's just that the possible adjustment and the positioning of the fractional part, and the possible adjustment of the seconds part need to be taken into account.
Thanks for all the help.
September 6, 2012 at 3:12 pm
Leaving off the day value from my first posting above and casting the result as a TIME datatype works for me. In every scenario I tried even if the end date is earlier than the start date or if the nanosecond part of one is greater/lesser than the other I get a valid result. As for an exhaustive testing against all possible date ranges or for performance I'll leave to others. This was an interesting problem! I have occasion to need date range calculations from time to time (though not at this level of precision) so will add this snippet to my tool box.
DECLARE
@StartTime DATETIME2(7)
,@EndTime DATETIME2(7)
SET @StartTime = '2012-04-26 13:53:43.0000000'
SET @EndTime = '2012-04-26 13:54:44.5448951'
--SET @StartTime = '2012-10-01 12:00:00.0130952'
--SET @EndTime = '2012-10-01 17:00:04.9845083'
--SET @StartTime = '2012-10-01 12:00:00.9845083'
--SET @EndTime = '2012-10-01 17:00:04.0130952'
--SET @StartTime = '2012-04-26 13:53:43.5448951'
--SET @EndTime = '2012-04-26 13:54:44.0000000'
SELECT
@StartTime AS stime
,@EndTime AS etime
,CAST(RIGHT('00' + CONVERT(VARCHAR,DATEDIFF(hour,@StartTime,@EndTime) % 24),2)
+ ':' + RIGHT('00' + CONVERT(VARCHAR,DATEDIFF(minute,@StartTime,@EndTime) % 60),2)
+ ':' + RIGHT('00' + CONVERT(VARCHAR,(DATEDIFF(second,@StartTime,@EndTime)) % 60),2)
+ '.' + RIGHT('0000000' + CONVERT(VARCHAR,(CAST((CAST(CAST(DATEPART(nanosecond,@EndTime) AS INT) AS DECIMAL(18,8))/100) - (CAST(CAST(DATEPART(nanosecond,@StartTime) AS INT) AS DECIMAL(18,8))/100) AS INT))),7)
AS TIME) AS timediff
Viewing 15 posts - 16 through 30 (of 36 total)
You must be logged in to reply to this topic. Login to reply