Skip to content

Instantly share code, notes, and snippets.

@ststeiger
Created July 1, 2015 15:15
Show Gist options
  • Save ststeiger/5b19251ec76cf880b667 to your computer and use it in GitHub Desktop.
Save ststeiger/5b19251ec76cf880b667 to your computer and use it in GitHub Desktop.
Overlapping number of days in two date ranges
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fu_RPT_OverlappingDateRangesDays]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[fu_RPT_OverlappingDateRangesDays]
GO
-- ======================================================================================================================
-- Author: Stefan Steiger
-- ALTER date: 11.06.2015
-- Alter date: 11.06.2015
-- Description: Calculate the number of overlapping days in two date-ranges
-- http://stackoverflow.com/questions/20836429/how-to-gets-the-number-of-overlapping-days-between-2-ranges
-- ======================================================================================================================
-- DECLARE @firstStart datetime
-- DECLARE @firstEnd datetime
-- DECLARE @secondStart datetime
-- DECLARE @secondEnd datetime
-- SET @firstStart = '01.01.2015'
-- SET @firstEnd = '31.01.2015'
-- SET @secondStart = '15.01.2014'
-- SET @secondEnd = '15.02.2015'
-- SELECT dbo.fu_RPT_OverlappingDateRangesDays( @firstStart, @firstEnd, @secondStart, @secondEnd )
CREATE FUNCTION [dbo].[fu_RPT_OverlappingDateRangesDays]
(
@firstStart datetime
,@firstEnd datetime
,@secondStart datetime
,@secondEnd datetime
)
RETURNS integer
AS
BEGIN
DECLARE @maxStart datetime
DECLARE @minEnd datetime
DECLARE @interval int
IF @firstStart IS NULL OR @firstEnd IS NULL OR @secondStart IS NULL OR @secondEnd IS NULL
-- RETURN 0
RETURN NULL
IF @firstEnd < @firstStart
RETURN 0
IF @secondEnd < @secondStart
RETURN 0
SET @maxStart = @secondStart
SET @minEnd = @secondEnd
IF @firstStart > @secondStart
SET @maxStart = @firstStart
IF @firstEnd < @secondEnd
SET @minEnd = @firstEnd
-- PRINT @maxStart
-- PRINT @minEnd
--SET @interval = DATEDIFF(DAY, @maxStart, @minEnd) + 1
SET @interval = {fn timestampdiff(SQL_TSI_DAY, @maxStart, @minEnd)} + 1
IF @interval < 0
SET @interval = 0
-- PRINT @interval
RETURN @interval
END
GO
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment