Luis Cazares - Thursday, December 28, 2017 10:37 AM
I don't if there are any published, but this one is an MTVF, so we know it'll be handicapped right out of the gate.
Here's one I wrote some time ago that performs pretty well. If anyone sees any ways to speed it up, I'm very open to them.
ALTER FUNCTION dbo.DateDiffDHMS(@StartDate Datetime,
@EndDate Datetime) RETURNS TABLE WITH SCHEMABINDING
AS
RETURN (
WITH cteParts AS (
SELECT Days = DATEDIFF(second, @StartDate, @EndDate) / 86400,
Hours = (DATEDIFF(second, @StartDate, @EndDate) / 3600) - (DATEDIFF(second, @StartDate, @EndDate) / 86400 * 24),
Minutes = (DATEDIFF(second, @StartDate, @EndDate) / 60) - (DATEDIFF(second, @StartDate, @EndDate) / 3600 * 60),
Seconds = DATEDIFF(second, @StartDate, @EndDate) % 60
)
SELECT Days, Hours, Minutes, Seconds,
DHMS = CONVERT(Varchar(8), Days) + ':' +
RIGHT('00' + CONVERT(Varchar(2), Hours), 2) + ':' +
RIGHT('00' + CONVERT(Varchar(2), Minutes), 2) + ':' +
RIGHT('00' + CONVERT(Varchar(2), Seconds), 2)
FROM cteParts
);
It does a heap of 100K rows in 120 ms and a heap of 1M rows in 1023 ms.
One limitation is that, because the base difference is in seconds, the max difference will be capped at 24,855 days, or just over 68 years. That's where it runs into the maximum value of an integer. It won't be good for everything, but it's good for everything we use it for.