Geek Answers Handbook

Date Match Ranges

I have two variables containing dates. DateStart and DateEnd (in SQL).
I also have two DatePicker (in WinForm).
I am importing two dates from a database and you need to do something complicated.

Thus, two variables create a date range, and two date pickers create a different date range.
How can I check if these date ranges overlap with an Sql request?

For example, (format yyyy / mm / dd)

DateStart = 2012/07/01 , DateEnd = 2012/07/31

 | DatePicker1 | DatePicker2 | Overlapping | -------------------------------------------- | 2012/07/15 | 2012/07/16 | True | -------------------------------------------- | 2012/07/31 | 2012/08/01 | True | -------------------------------------------- | 2012/06/20 | 2012/07/01 | True | -------------------------------------------- | 2012/08/01 | 2012/09/01 | False | --------------------------------------------

I know this got a little messed up, but I didn't know how else to ask about it.

+4
source share
2 answers

Two date ranges overlap if the beginning of the first range is before the end of the second range, and the end of the first range is after the start of the second range. So:

 where DateStart <= @DatePicker2 and DateEnd >= @DatePicker1

A good explanation is this way .

+2
source

Use this function

 /* * Tests if two given periods overlap each other. * * @TS Test period start * @TE Test period end * @BS Base period start * @BE Base period end */ CREATE FUNCTION [fn_DateTime_Overlap] ( @TS DATETIME = NULL, @TE DATETIME = NULL, @BS DATETIME = NULL, @BE DATETIME = NULL ) RETURNS BIT AS BEGIN -- More simple? -- return !((TS < BS && TE < BS) || (TS > BE && TE > BE)); -- The version below, without comments -- (TS >= BS && TS < BE) || (TE <= BE && TE > BS) || (TS <= BS && TE >= BE) IF ( -- 1. Case: -- TS-------TE -- BS------BE -- TS is after BS but before BE (@TS >= @BS AND @TS < @BE) -- 2. Case -- TS-------TE -- BS---------BE -- TE is before BE but after BS OR (@TE <= @BE AND @TE > @BS) -- 3. Case -- TS----------TE -- BS----BE -- TS is before BS and TE is after BE OR (@TS <= @BS AND @TE >= @BE) ) RETURN 1 RETURN 0 END
0
source

More articles:


All Articles