Technical Article

Difference between dates displayed in days, hours, minutes and seconds

,

-- Mostrar una diferencia de solo fechas
SELECT dbo.ufn_DifferenceBetweenDateTimes('2017-10-03 00:00:00', '2017-10-12 00:00:00')
-- Return: 9d 00:00:00
-- Mostrar una diferencia en minutos
SELECT dbo.ufn_DifferenceBetweenDateTimes('2017-10-05 07:30:00', '2017-10-05 07:55:00')
-- Return: 0d 00:25:00
-- Mostrar una diferencia en la misma fecha
SELECT dbo.ufn_DifferenceBetweenDateTimes('2017-09-01 01:46:00', '2017-09-01 10:55:00')
-- Return: 0d 09:09:00
-- Mostrar una diferencia de dias
SELECT dbo.ufn_DifferenceBetweenDateTimes('2017-11-21 08:21:00', '2017-12-06 18:00:00')
-- Return: 15d 09:39:00
CREATE FUNCTION dbo.ufn_DifferenceBetweenDateTimes
(
@Date1 Datetime, -- Dia inicio
@Date2 Datetime -- Dia fin
-- Formato: #d HH:MM:SS
-- Sample:
-- Set dateformat ymd
-- SELECT dbo.ufn_DifferenceBetweenDateTimes('2017-10-01 01:46:00', '2017-10-17 10:45:00')
-- Return: 16d 08:59:00
)
RETURNS VARCHAR(MAX)
AS 
BEGIN
DECLARE @OUT VARCHAR(100)
DECLARE @segundos BIGINT

SET @segundos = ( SELECT datediff(SECOND,@Date1, @Date2) ) -- AS Segundos
SET @segundos = ISNULL(@segundos, 0)
SET @OUT ='0d 00:00:00'

IF (@segundos < 0)
RETURN @OUT

SET @OUT = CONVERT(VARCHAR, FLOOR(@segundos / 86400) ) + 'd ' -- days
SET @segundos = @segundos % 86400
SET @OUT = @OUT + Right('0'+ CONVERT(VARCHAR, FLOOR(@segundos / 3600)),2) + ':' -- hours
SET @segundos = @segundos % 3600
SET @OUT = @OUT + Right('0'+ CONVERT(VARCHAR, FLOOR(@segundos / 60)),2) + ':' -- minutes
SET @OUT = @OUT + Right('0'+ CONVERT(VARCHAR, @segundos % 60),2) -- seconds

-------------
RETURN @OUT
-------------
END

Rate

1 (2)

You rated this post out of 5. Change rating

Share

Share

Rate

1 (2)

You rated this post out of 5. Change rating