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


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


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 !!


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)

	--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
		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


	RETURN @ReturnCoverDays;

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.

2 Replies to “Dates Overlap – Get the days count overlapping between 2 date ranges (SQL)”

  1. Hi,

    Thx for this function!
    If I’m right this function includes another “custom” function!? “dbo.fn_ConvertIntToDate”

    Do you also have the query to create this 2nd function?

    Thx and Best,

Leave a Reply

Your email address will not be published. Required fields are marked *