September 6, 2012 at 3:18 pm
bmahf (9/6/2012)
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.
I don't see those issues with my code, but I too did not do an exhaustive check against all possible values 🙂 .
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 5:53 pm
Erland has a Connect request that seems relevant to this discussion:
http://connect.microsoft.com/SQLServer/feedback/details/320998/add-datediff-big
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
September 7, 2012 at 2:09 am
ScottPletcher (9/6/2012)
bmahf (9/6/2012)
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.
I don't see those issues with my code, but I too did not do an exhaustive check against all possible values 🙂 .
Your code is spot on, Scott, with all the values we've tested so far:
;With MyCTE (RowID, aTime, bTime)
AS
(
SELECT 1, CONVERT(datetime2,'1900-01-01 00:08:00.0000001'), CONVERT(datetime2,'1900-01-01 00:09:18.3610010')
UNION ALL SELECT 2, '1900-01-01 00:08:00.0000001', '1900-01-01 00:13:50.705002'
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.000000111', '1900-01-01 00:00:00.000000222'
UNION ALL SELECT 6, '1900-01-01 00:00:00.000000222', '1900-01-01 00:00:00.000000111'
UNION ALL SELECT 7, '1900-01-01 00:10:00.000000111', '1900-01-01 00:00:00.000000222'
UNION ALL SELECT 8, '1900-01-01 00:10:00.999998811', '1900-01-01 00:10:01.000002222'
UNION ALL SELECT 9, '1900-01-01 00:10:01.999998811', '1900-01-01 00:10:00.000002222'
UNION ALL SELECT 10, '1900-01-01 00:08:00.0000001', '1900-01-01 00:13:50.705'
UNION ALL SELECT 11, '1900-01-01 00:09:18.361', '1900-01-01 03:13:50.7050001'
UNION ALL SELECT 12, '1900-01-01 23:09:18.361', '1900-01-02 02:13:50.7050001'
UNION ALL SELECT 13, '1900-01-01 00:00:00.0000001', '1900-01-01 00:00:00.0000002'
UNION ALL SELECT 14, '1900-01-01 00:00:00.0000002', '1900-01-01 00:00:00.0000001'
UNION ALL SELECT 15, '1900-01-01 00:10:00.0000001', '1900-01-01 00:00:00.0000002'
UNION ALL SELECT 16, '1900-01-01 00:10:01.9999988', '1900-01-01 00:10:00.0000022'
UNION ALL SELECT 17, '1900-01-01 00:10:00.9999998', '1900-01-01 00:10:01.0000001'
UNION ALL SELECT 18, '1900-01-01 00:10:00.0000023', '1900-01-01 00:10:01.0000022'
UNION ALL SELECT 19, '1900-01-01 00:10:00.0000022', '1900-01-01 00:10:01.0000022'
UNION ALL SELECT 20, '1900-01-01 00:10:00.0000021', '1900-01-01 00:10:01.0000022'
UNION ALL SELECT 21, '1900-01-01 00:10:00.0000020', '1900-01-01 00:10:01.0000022'
)
SELECT
RowID,
aTime,
bTime,
SimpleProof = CASE WHEN ABS(DATEDIFF(ss,aTime,bTime)) < 11072
THEN CAST(DATEADD(microsecond,ABS(DATEDIFF(microsecond,aTime, bTime)),CAST('1900' AS DATETIME2)) AS TIME(7))
ELSE CAST(DATEADD(millisecond,ABS(DATEDIFF(millisecond,aTime, bTime)),CAST('1900' AS DATETIME2)) AS TIME(7)) END,
TimeResult1 = -- ChrisR (5 date functions, 2 datatype conversions)
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)),
TimeResult2 = -- ChrisM (5 date functions, 2 datatype conversions)
CAST(
DATEADD(ns,(DATEPART(ns,bTime)-DATEPART(ns,aTime)) * (CASE WHEN aTime < bTime THEN 1 ELSE -1 END),
DATEADD(ss,ABS(DATEDIFF(ss,aTime,bTime)),CAST('1900' AS datetime2))
) AS TIME(7)),
TimeDiff = -- ScottP (8 date functions, 3 datatype conversions)
CAST(
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)
AS TIME(7)),
TimeDiff = -- Steven W (5 date functions, 10 datatype conversions)
-- fails with datetime conversion error on rows 3,4,7,9,11,12,15,16,18
-- returns incorrect result for rows 8 & 17 using starttime & endtime
-- returns incorrect result for rows 6,8,14 & 17 using atime & btime
CAST(RIGHT('00' + CONVERT(VARCHAR,DATEDIFF(hour,aTime,bTime) % 24),2)
+ ':' + RIGHT('00' + CONVERT(VARCHAR,DATEDIFF(minute,aTime,bTime) % 60),2)
+ ':' + RIGHT('00' + CONVERT(VARCHAR,(DATEDIFF(second,aTime,bTime)) % 60),2)
+ '.' + RIGHT('0000000' + CONVERT(VARCHAR,(CAST((CAST(CAST(DATEPART(nanosecond,EndTime) AS INT) AS DECIMAL(18,8))/100)
- (CAST(CAST(DATEPART(nanosecond,aTime) AS INT) AS DECIMAL(18,8))/100) AS INT))),7)
AS TIME)
From (
SELECT
RowID,
Sec = (DATEDIFF(second, aTime, btime)),
NanoSec = (DATEPART(ns,btime)-DATEPART(ns,atime)),
aTime,
bTime,
StartTime = CASE WHEN aTime < bTime THEN aTime ELSE bTime END,
EndTime = CASE WHEN aTime < bTime THEN bTime ELSE aTime END
FROM MyCTE
) d
--WHERE RowID NOT IN (3,4,7,9,11,12,15,16,18) -- rows which may fail
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 7, 2012 at 2:11 am
bmahf (9/6/2012)
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.
Interested to see your new version, Bruce.
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 7, 2012 at 12:00 pm
TimeResult1 = -- ChrisR (5 date functions, 2 datatype conversions)
TimeResult2 = -- ChrisM (5 date functions, 2 datatype conversions)
TimeDiff = -- ScottP (8 date functions, 3 datatype conversions)
TimeDiff = -- Steven W (5 date functions, 10 datatype conversions)
These totals aren't quite accurate.
My code uses only columns native to the table.
All other code use derived column that require pre-computing.
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 7, 2012 at 6:19 pm
ScottPletcher (9/7/2012)
TimeResult1 = -- ChrisR (5 date functions, 2 datatype conversions)
TimeResult2 = -- ChrisM (5 date functions, 2 datatype conversions)
TimeDiff = -- ScottP (8 date functions, 3 datatype conversions)
TimeDiff = -- Steven W (5 date functions, 10 datatype conversions)
These totals aren't quite accurate.
My code uses only columns native to the table.
All other code use derived column that require pre-computing.
Looking at the execution plans for:
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,
TimeDiff = -- ScottP
CONVERT(varchar(8), DATEADD(SECOND, DATEDIFF(SECOND, aTime , bTime)
- CASE WHEN DATEPART(NANOSECOND, aTime) > DATEPART(NANOSECOND, bTime) THEN 1 ELSE 0 END, 0), 108) +
'.' + RIGHT(REPLICATE('0', 7) +
CAST(((CASE WHEN DATEPART(NANOSECOND, aTime) > DATEPART(NANOSECOND, bTime) THEN 1000000000 ELSE 0 END +
DATEPART(NANOSECOND, bTime) - DATEPART(NANOSECOND, aTime)) / 100) AS varchar(7)), 7)
FROM MyCTE;
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,
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))
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;
...the Compute Scalars contain:
ChrisM : 1 conversion, 6 functions
ScottP: 2 conversions, 8 functions
Note: The ChrisM code returns a time(7) whereas ScottP returns varchar(8)
-- ScottP
[Expr1043] = Scalar Operator(
CONVERT(varchar(8),
dateadd(second,
datediff(second,
CONVERT_IMPLICIT(datetimeoffset(7),[Union1037],0),
CONVERT_IMPLICIT(datetimeoffset(7),[Union1038],0))-
CASE WHEN
datepart(nanosecond,[Union1037])>
datepart(nanosecond,[Union1038])
THEN (1) ELSE (0) END,'1900-01-01 00:00:00.000'),108)+'.'+ right('0000000'+
CONVERT(varchar(7),(
CASE WHEN
datepart(nanosecond,[Union1037])>
datepart(nanosecond,[Union1038])
THEN (1000000000) ELSE (0) END+
datepart(nanosecond,[Union1038])-
datepart(nanosecond,[Union1037]))/(100),0),(7)))
-- ChrisM
[Expr1043] = Scalar Operator(
CONVERT(time(7),
dateadd(nanosecond,
CASE WHEN [Union1037]<[Union1038] THEN
datepart(nanosecond,[Union1038])-
datepart(nanosecond,[Union1037])
ELSE
datepart(nanosecond,[Union1037])-
datepart(nanosecond,[Union1038]) END,
dateadd(second,abs(datediff(second,
CONVERT_IMPLICIT(datetimeoffset(7),[Union1037],0),
CONVERT_IMPLICIT(datetimeoffset(7),[Union1038],0))),'1900-01-01 00:00:00.0000000')),0))
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
September 7, 2012 at 6:31 pm
Updated my SQLCLR function to produce the results needed in this thread:
[font="Courier New"]
public static TimeSpan GetIntervalAsTime(DateTime Start, DateTime End)
{
return
(Start <= End) ?
End.Subtract(Start) :
End.AddDays(1).Subtract(Start);
}
[/font]
Test data:
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
TimeDiff = dbo.GetIntervalAsTime (aTime, bTime)
FROM MyCTE;
Updated assembly and function definition:
CREATE ASSEMBLY Intervals
FROM 0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A2400000000000000504500004C010300B59A4A500000000000000000E00002210B0108000008000000060000000000007E260000002000000040000000004000002000000002000004000000000000000400000000000000008000000002000000000000030040850000100000100000000010000010000000000000100000000000000000000000282600005300000000400000A802000000000000000000000000000000000000006000000C0000008C2500001C0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000080000000000000000000000082000004800000000000000000000002E7465787400000084060000002000000008000000020000000000000000000000000000200000602E72737263000000A80200000040000000040000000A0000000000000000000000000000400000402E72656C6F6300000C0000000060000000020000000E000000000000000000000000000040000042000000000000000000000000000000006026000000000000480000000200050090200000FC0400000100000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000133003002C000000010000110203280500000A2D1A0F0123000000000000F03F280600000A0A120002280700000A2A0F0102280700000A2A1E02280800000A2A42534A4201000100000000000C00000076322E302E35303732370000000005006C00000048010000237E0000B40100007801000023537472696E6773000000002C03000008000000235553003403000010000000234755494400000044030000B801000023426C6F620000000000000002000001471502000900000000FA2533001600000100000008000000020000000200000002000000080000000400000001000000010000000200000000000A0001000000000006003D00360006004400360006004D00360006008B00780013009F0000000600CE00AE000600EE00AE000A003D01220100000000010000000000010001000100100018000000050001000100502000000000960056000A0001008820000000008618680013000300000001006E00000002007400210068001700310068001D00390068001300410068001300190052016A0119006501720119006D0178010900680013002000230022002E000B0084012E0013008D012E001B0096017F010480000000000000000000000000000000000C01000002000000000000000000000001002D000000000002000000000000000000000001001601000000000000003C4D6F64756C653E00496E74657276616C732E646C6C0055736572446566696E656446756E6374696F6E73006D73636F726C69620053797374656D004F626A6563740054696D655370616E004461746554696D6500476574496E74657276616C417354696D65002E63746F7200537461727400456E640053797374656D2E446961676E6F73746963730044656275676761626C6541747472696275746500446562756767696E674D6F6465730053797374656D2E52756E74696D652E436F6D70696C6572536572766963657300436F6D70696C6174696F6E52656C61786174696F6E734174747269627574650052756E74696D65436F6D7061746962696C69747941747472696275746500496E74657276616C730053797374656D2E44617461004D6963726F736F66742E53716C5365727665722E5365727665720053716C46756E6374696F6E417474726962757465006F705F4C6573735468616E4F72457175616C004164644461797300537562747261637400000000032000000000003D49F20ED1D67D44ACCAD7654574023D0008B77A5C561934E0890800021109110D110D0320000105200101111504200101088146010004005455794D6963726F736F66742E53716C5365727665722E5365727665722E446174614163636573734B696E642C2053797374656D2E446174612C2056657273696F6E3D322E302E302E302C2043756C747572653D6E65757472616C2C205075626C69634B6579546F6B656E3D623737613563353631393334653038390A446174614163636573730000000054557F4D6963726F736F66742E53716C5365727665722E5365727665722E53797374656D446174614163636573734B696E642C2053797374656D2E446174612C2056657273696F6E3D322E302E302E302C2043756C747572653D6E65757472616C2C205075626C69634B6579546F6B656E3D623737613563353631393334653038391053797374656D446174614163636573730000000054020F497344657465726D696E6973746963015402094973507265636973650107000202110D110D052001110D0D0620011109110D040701110D0801000200000000000801000800000000001E01000100540216577261704E6F6E457863657074696F6E5468726F77730100000000000000B59A4A50000000000200000080000000A8250000A807000052534453059586A87E0DEA40A2186106E3FD4C5B07000000633A5C55736572735C5061756C2057686974655C446F63756D656E74735C56697375616C2053747564696F20323031305C50726F6A656374735C4461746162617365325C4461746162617365325C6F626A5C52656C656173655C496E74657276616C732E706462005026000000000000000000006E2600000020000000000000000000000000000000000000000000006026000000000000000000000000000000005F436F72446C6C4D61696E006D73636F7265652E646C6C0000000000FF25002040000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001001000000018000080000000000000000000000000000001000100000030000080000000000000000000000000000001000000000048000000584000004C02000000000000000000004C0234000000560053005F00560045005200530049004F004E005F0049004E0046004F0000000000BD04EFFE00000100000000000000000000000000000000003F000000000000000400000002000000000000000000000000000000440000000100560061007200460069006C00650049006E0066006F00000000002400040000005400720061006E0073006C006100740069006F006E00000000000000B004AC010000010053007400720069006E006700460069006C00650049006E0066006F0000008801000001003000300030003000300034006200300000002C0002000100460069006C0065004400650073006300720069007000740069006F006E000000000020000000300008000100460069006C006500560065007200730069006F006E000000000030002E0030002E0030002E00300000003C000E00010049006E007400650072006E0061006C004E0061006D006500000049006E00740065007200760061006C0073002E0064006C006C0000002800020001004C006500670061006C0043006F00700079007200690067006800740000002000000044000E0001004F0072006900670069006E0061006C00460069006C0065006E0061006D006500000049006E00740065007200760061006C0073002E0064006C006C000000340008000100500072006F006400750063007400560065007200730069006F006E00000030002E0030002E0030002E003000000038000800010041007300730065006D0062006C0079002000560065007200730069006F006E00000030002E0030002E0030002E0030000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000002000000C000000803600000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
WITH PERMISSION_SET = SAFE;
GO
CREATE FUNCTION dbo.GetIntervalAsTime
(
@Start datetime2(7),
@End datetime2(7)
)
RETURNS time(7)
AS EXTERNAL NAME Intervals.UserDefinedFunctions.GetIntervalAsTime;
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 7 posts - 31 through 36 (of 36 total)
You must be logged in to reply to this topic. Login to reply