Log in

Leonard Sperry

Whimsical musings of a Software Engineer, Seattlite, and Father

SQL - Overlapping Dates

by Leonard

Every now and again, I write something where I really impress myself. Today, was one of those days.  I had this problem where I need to retrieve all the events where the dates overlapped another giving date range for the purpose of rendering them on a calendar.  All the events and data are in SQL, so I needed to figure out to do it in SQL.  I have solved a similar problem in C# using Min() and Max() functions. However, those functions do not exist.  So, I came up with a different solution.

This solution is based on a simple fact.  If you find the difference from the start and end dates of the first rang with all the start and end dates of the second range, all the answers will either be all positive or all negative. If there is a mix of positive and negative differences, the dates will overlap.

 

Enjoy

CREATE FUNCTION [dbo].[DatesOverlap]
(
	@StartDate1 DATETIME,
	@EndDate1 DATETIME,
	@StartDate2 DATETIME,
	@EndDate2 DATETIME
)
RETURNS BIT
AS
BEGIN
	RETURN 
		SIGN(
			ABS(
				SIGN(DATEDIFF(SECOND ,@StartDate1, @StartDate2)) + 
				SIGN(DATEDIFF(SECOND ,@StartDate1, @EndDate2)) + 
				SIGN(DATEDIFF(SECOND ,@EndDate1, @StartDate2)) + 
				SIGN(DATEDIFF(SECOND ,@EndDate1, @EndDate2))
				) - 4
			)* -1
END

TAGS:

blog comments powered by Disqus

About the author

Leonard is a Software Engineer who works for a web CMS firm in Seattle Washington.  He is the primary technical contractor for Seattle Engraving.   Where he built a custom CRM to help manage customer interactions and will soon be rolling out a new and improved website based on Orchard CMS.

He has lived in the Puget Sound region for most of his life, and is a father of 3.

Month List