The built in SQL dateDiff function has issues when trying to calculate the difference between dates that are far apart when you request a high precision. There are solutions available which request a difference with a lower precision and multiply that by a constant, however I was looking for a more absolute answer, so I turned to the .Net runtime.
The built in function:
dateDiff(MILLISECOND, '01/01/2010', '01/01/2011')
Returns with the error:
Msg 535, Level 16, State 0, Line 4
The datediff function resulted in an overflow. The number of dateparts separating two date/time instances is too large. Try to use datediff with a less precise datepart.
However my new function:
[dbo].[DateDiffMs]('01/01/2010', '01/01/2011')
Returns:
31536000000
Success!
This is possible by creating a User Defined Function in C# which is then deployed to the database.
In visual studio, create a new Sql Database CLR project and create the very simple function:
public partial class UserDefinedFunctions
{
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlInt64 DateDiffMs(DateTime from, DateTime to)
{
var span = to.Subtract(from);
return new SqlInt64((long)span.TotalMilliseconds);
}
};
Deploy this to your database and you then have the ability to get the number of milliseconds between any date.
Testing
To test the speed implications I created two quick recursive CTE's which record how long it takes to run 100000 operations.
The first tests the standard Date Diff:
declare @loopSize int = 1000000;
declare @start datetime = null;
declare @end datetime = null;
SELECT @start = GETDATE();
with cte_1(result, myCounter)
AS
(
select DATEDIFF(SECOND, '01/01/2010', '01/01/2011'), 1
union all
select result, myCounter + 1 FROM cte_1
where myCounter < @loopSize
)
select * from cte_1 OPTION (MAXRECURSION 0);
SELECT @end= GETDATE();
SELECT [dbo].[TicksDiff](@start, @end)
GO
GO
The second tests the .Net Function:
declare @loopSize int = 1000000;
declare @start datetime = null;
declare @end datetime = null;
SELECT @start = GETDATE();
with cte_1(result, myCounter)
AS
(
select [dbo].[DateDiffSecs]('01/01/2010', '01/01/2011'), 1
union all
select result, myCounter + 1 FROM cte_1
where myCounter < @loopSize
)
select * from cte_1 OPTION (MAXRECURSION 0);
SELECT @end= GETDATE();
SELECT [dbo].[TicksDiff](@start, @end)
GO
--select DATEDIFF(SECOND, '01/01/2010', '01/01/2011');
GO
Results
DateDiff - 184100000 ticks
DateDiffSecs - 190300000 ticks
The .NET CLR method only takes 3.3% longer to run than the built in function. I think this is tolerable!
Subscribe