Dates Overlap – Get the days count overlapping between 2 date ranges (SQL)

Hi,

Well today’s post is a simple one, but this one which gave me a good fight to get the trick right !!!

Situation:

I have 2 date ranges, and I need to know the number of days over-lapping between these 2 date ranges

The above highlighted red area is what I am interested in !!

Resolution:

I just create a Scalar user defined function (MSSQL) as below and call it in my query with the necessary parameters to obtain the results.

CREATE FUNCTION [dbo].[fn_ComputeRangeDaysFromBoundaries](@OuterStart INT,@OuterEnd INT,@CheckStart INT,@CheckEnd INT)
RETURNS INT
AS 
BEGIN

	--DECLARE @OuterStart INT = 20180808;
	--DECLARE @OuterEnd INT = 20180826;

	--DECLARE @CheckStart INT = 20180805;
	--DECLARE @CheckEnd INT = 20180820;

	DECLARE @ReturnCoverDays INT = 0;

	IF (@OuterStart <= @OuterEnd) AND (@CheckStart <= @CheckEnd) --Check for valid date ranges
	BEGIN
		
		DECLARE @DateAPart INT , @DateBPart INT;


		IF(@OuterStart = @CheckStart)
			SET @DateAPart = @CheckStart
		ELSE IF (@OuterStart < @CheckStart)
			SET @DateAPart = @CheckStart
		ELSE IF (@CheckStart < @OuterStart)
			SET @DateAPart = @OuterStart


		IF(@OuterEnd = @CheckEnd)
			SET @DateBPart = @CheckEnd
		ELSE IF(@OuterEnd < @CheckEnd)
			SET @DateBPart = @OuterEnd
		ELSE IF(@CheckEnd < @OuterEnd)
			SET @DateBPart = @CheckEnd

		--SELECT @DateAPart , @DateBPart

		IF (@DateAPart <= @DateBPart)
			SET @ReturnCoverDays = DATEDIFF(DAY , dbo.fn_ConvertIntToDate(@DateAPart), dbo.fn_ConvertIntToDate(@DateBPart)) + 1

		
	END


	RETURN @ReturnCoverDays;
END

And that’s it…

The solution is above which made my query so much more optimised and fast (rather than leveraging Date/Time tables to join upon and get the day count between Start/End dates).

But it was a hard one to derive (due to boundary conditions).

Hope it helps.