December 27, 2017 at 6:42 pm
Comments posted to this topic are about the item Difference between dates displayed in days, hours, minutes and seconds
December 28, 2017 at 10:37 am
Didn't we gave some faster alternatives in the forums for this function?
December 28, 2017 at 11:54 am
And why return a VARCHAR(MAX) value? Are you really going to have a value that needs 2GB?
Plus, this query seems to work just fine:
SELECT CAST(DATEDIFF(DAY,0,DATEADD(SECOND,DATEDIFF(SECOND,StartDate, EndDate),0)) AS VARCHAR(30)) + 'd ' + CONVERT(VARCHAR(8),DATEADD(SECOND,DATEDIFF(SECOND,StartDate, EndDate),0),108)
FROM
(VALUES ('2017-10-03 00:00:00', '2017-10-12 00:00:00')
,('2017-10-05 07:30:00', '2017-10-05 07:55:00')
,('2017-09-01 01:46:00', '2017-09-01 10:55:00')
,('2017-11-21 08:21:00', '2017-12-06 18:00:00')
)dt(StartDate,EndDate);
Not sure what forum thread you are talking about, Luis.
December 28, 2017 at 12:06 pm
Lynn Pettis - Thursday, December 28, 2017 11:54 AMNot sure what forum thread you are talking about, Luis.
Here's the thread.
https://www.sqlservercentral.com/Forums/1913258/Difference-between-dates-displayed-in-days-hours-minutes-and-seconds
December 28, 2017 at 12:09 pm
Luis Cazares - Thursday, December 28, 2017 10:37 AMDidn't we gave some faster alternatives in the forums for this function?
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.
December 28, 2017 at 12:21 pm
Luis Cazares - Thursday, December 28, 2017 12:06 PMLynn Pettis - Thursday, December 28, 2017 11:54 AMNot sure what forum thread you are talking about, Luis.Here's the thread.
https://www.sqlservercentral.com/Forums/1913258/Difference-between-dates-displayed-in-days-hours-minutes-and-seconds
I didn't even know about that thread. Your post here (to the script) was the first I saw of the topic. Thanks for posting it.
January 3, 2018 at 2:40 am
I've wrote an article describing the method of age calculations expressed on all sorts of formats:
http://www.sqlservercentral.com/articles/Datetime+conversions/153316/
Amongst others, it can return a date difference in days-hours-minutes-seconds format.
And it does not have the 68 years limitation. 🙂
_____________
Code for TallyGenerator
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply