Below is alternative method that's closer to the original technique (for good or ill). Btw, there's a minor bug in the other code that doesn't reduce the day count if the first date has a later time than the second date.
SELECT *
FROM (VALUES
('2017-10-01 10:46:00', '2017-10-17 01:45:00'),
('2017-10-17 10:46:00', '2017-10-01 01:45:00')
) dates(date1,date2)
CROSS APPLY dbo.ifn_DifferenceBetweenDates( date1, date2 )
CROSS APPLY dbo.ufn_DifferenceBetweenDates2 ( date1, date2 )
CREATE FUNCTION dbo.ufn_DifferenceBetweenDates2
(
@Date1 datetime, --Dia inicio
@Date2 datetime --Dia fin
)
RETURNS TABLE
AS
RETURN (
SELECT CAST(CAST(dias AS varchar(5)) + 'd ' +
CONVERT(varchar(8), DATEADD(SECOND, segundos, 0) , 8) AS varchar(16)) AS DiferenciaenHoras
FROM (
SELECT CASE WHEN @Date1 > @Date2 THEN 0 ELSE DATEDIFF(SECOND, @Date1, @Date2) / (60*60*24) END AS dias,
CASE WHEN @Date1 > @Date2 THEN 0 ELSE DATEDIFF(SECOND, @Date1, @Date2) % (60*60*24) END AS segundos
) AS cálculos
)
GO
Your method is prone to the limits of DATEDIFF(SECOND, which results in the following error...
Msg 535, Level 16, State 0, Line 8
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.
The maximum period that can be resolved by DATEDIFF(SECONDS is "only" 68 years, 1 month, 19 days, and 03:14:07.000.
Since this is a 2016 forum, you could use the new DATEDIFF_BIG function that they created to cover up the mistakes they made with not allowing direct datetime math for the DATE and DATETIME2 datatypes.