none
Query for Overlapping Dates

    Вопрос

  • Dear All,

    I  need to display records that don't overlapp  my entire date range . I have @startDate and @endDate as input parameter.

    Lets suppose my @startDate = 02/01/2012 and @endDate= 02/29 /2012.

    In the table I have the following records


    Start-Date       End-Date

    02/01/2000  -  02/15 /2000
    02/01/2014  -  02/29/2015
    02/01/2012 -   02/29 /2012
    05/01/2005 -   05/01/2020
    02/01/2011 -   02/29 /2012
    01/31/2012 -   02/15/2012

    So I need a query that will display the following records:

    Start-Date       End-Date

    02/01/2000  -  02/15 /2000
    02/01/2014  -  02/29/2015
    01/31/2012 -   02/15/2012

    Any help would be greatly appreciated

    cheers,
    Sammy

    27 февраля 2012 г. 13:16

Ответы

  • Hi Rishabh,

    What if I have the same difference between two dates but in different years?

    Sammy,

    Try this code:

    USE [tempdb]; GO CREATE TABLE [DateRanges] ( [StartDate] date, [EndDate] date ); GO INSERT INTO [DateRanges] VALUES ('20000201', '20000215'); INSERT INTO [DateRanges] VALUES ('20140201', '20150228'); --no 29th of Febr in 2015. it is not a leap year. INSERT INTO [DateRanges] VALUES ('20120201', '20120229'); INSERT INTO [DateRanges] VALUES ('20050501', '20200501'); INSERT INTO [DateRanges] VALUES ('20110201', '20120215'); INSERT INTO [DateRanges] VALUES ('20120131', '20120215'); GO DECLARE @StartDate date = '20120201'; DECLARE @EndDate date = '20120229'; SELECT * FROM [DateRanges] WHERE ([StartDate] < @StartDate AND [EndDate] < @EndDate) OR ([StartDate] > @StartDate AND [EndDate] > @EndDate) OR ([StartDate] < @StartDate AND [EndDate] > @EndDate)


    I hope it helps.

    Janos


    There are 10 type of people. Those who understand binary and those who do not.
    My Blog


    • Изменено Janos Berke 27 февраля 2012 г. 13:50 fixed the where statement
    • Помечено в качестве ответа Kalman TothModerator 5 марта 2012 г. 15:04
    27 февраля 2012 г. 13:47
  • As you probably discovered as you started writing your query, it's a bit more complex than meets the eye:  Here are all the conditions (business rules, not necessarily your actual SQL comparisons) you'd need to account for:  (fabricate them all in your test data, at the very least, to make sure whatever algorithm you use accomodates them all).

    1. Compare range starts before and ends after your @start/@end
    2. Compare range starts before, ends at the exact start time of your @start/@end
    3. Compare range starts before, ends sometime within your @start/@end
    4. Compare range starts before, ends at the exact end time of your @start/end
    5. Compare range starts at the exact start time, ends before your @start/@end
    6. Compare range starts at the exact start, ends at the exact end of your @start/@end
    7. Compare range starts at the exact start, ends after the end of your @start/@end
    8. Compare range starts within (after start time) and ends within (before end time) of your @start/@end
    9. Compare range starts within (after start time) and ends at exact end time of your @start/@end
    10. Compare range starts within (after start time) and ends after your @start/@end
    11. Compare range starts at the exact same time as the end of your @start/@end and ends afterwards.

    It's very easy to treat this as a more simple problem than it actually is.  Start Anding and Oring and cover all your bases. 

    Scenario number 1 is probably the second most often overlooked scenario by most people.

    And the most often overlooked scenario is not accommodating for the END of the day for your end date range...   For example, simply specifying '2/29/2012' as your end date in a comparison operator means you are specifying '2/29/2012 00:00:00.000' unless you specify otherwise.  So, if you were to compare that against a value that fell on your end date, and if that value had a time component (2/29/2012 at 3 p.m., for example) then your comparison would be incorrect.   I suggest adding a day, and doing a "less than 3/1/2012 00:00:00.000", rather than a less-than-or-equal '2-29-2012 23:59:59.997'



    • Изменено johnqflorida 27 февраля 2012 г. 14:34 fix typos
    • Помечено в качестве ответа Kalman TothModerator 5 марта 2012 г. 15:04
    27 февраля 2012 г. 14:29
  • Hi,

    Based on the new reqs, plese check this script:

    USE [tempdb];
    GO
    CREATE TABLE [DateRanges]
    (
    	[StartDate] datetime,
    	[EndDate]	datetime
    );
    GO
     
     
    INSERT INTO [DateRanges] VALUES ('2011-02-01 02:00:00.000', '2099-12-31 23:59:00.000');
    INSERT INTO [DateRanges] VALUES ('2011-02-01 02:00:00.000', '2099-12-31 23:59:00.000'); 
    INSERT INTO [DateRanges] VALUES ('1990-02-01 02:00:00.000', '2099-12-31 23:59:00.000');
    INSERT INTO [DateRanges] VALUES ('2011-02-01 02:00:00.000', '2099-12-31 23:59:00.000');
    INSERT INTO [DateRanges] VALUES ('2011-02-01 02:00:00.000', '2099-12-31 23:59:00.000');
    GO
    DECLARE @StartDate datetime = '2010-02-01 02:00:00.000';
    DECLARE @EndDate datetime = '2099-12-31 23:59:00.000';
    SELECT 
    	* 
    FROM 
    	[DateRanges]
    WHERE
    	([StartDate] >= @StartDate AND [EndDate] <= @EndDate)

    I hope it helps.

    J.


    There are 10 type of people. Those who understand binary and those who do not.
    My Blog

    • Помечено в качестве ответа Kalman TothModerator 5 марта 2012 г. 15:04
    27 февраля 2012 г. 14:44
  • To cover this case, use this criterion:

    DECLARE @StartDate datetime = '2012-02-01 02:00:00.000';
    DECLARE @EndDate datetime = '2099-12-31 23:59:00.000';
    
    select * from [DateRanges] 
    where EndDate <@EndDate and StartDate <@StartDate 


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    • Помечено в качестве ответа Kalman TothModerator 5 марта 2012 г. 15:05
    27 февраля 2012 г. 15:28
  • Ok, I've officially spent too much "fun time" in the forums today:  :-)  

    Here's two things:  Part 1, populates all possible scenarios, so as you experiment with other approaches if you choose to do that, you have a definitive table with all the possibilities included. 

    Part 2, my approach, which I believe has caught it all.  Note that I use a somewhat non-traditional approach at the end (the EXCEPT statement), simply to avoid having to use 'negated' comparisons.  Sometimes, you compare developer time, ease of maintainability for the life cycle of the query, and if you can waste a CPU tick or two, you can opt to make life easier for yourself.

    Create_Temp_table:
    		Declare @LIST TABLE 
    		(
    			TestCaseOrder int identity primary key,  -- Just to keep results in same order as test cases
    			ExpectedResult varchar(99),				 -- Just to make it easier to interpret results
    			[StartDate] datetime,
    			[EndDate]	datetime
    		);
     
    Populate_all_test_scenarios: 
    		INSERT INTO @LIST VALUES ('Should show', '2000/02/01', '2000/02/15');  -- starts/ends before
    		INSERT INTO @LIST VALUES ('Should show', '2014/02/01', '2015/02/28');  -- starts/ends after
    		INSERT INTO @LIST VALUES ('Should show', '2012/01/30', '2012/01/31');  -- starts/ends before
    		INSERT INTO @LIST VALUES ('Should show', '2012/03/01', '2012/03/02');  -- starts/ends after
    
    		INSERT INTO @LIST VALUES ('Do not show', '2012/02/01 00:00:00', '2012/02/29');
    		INSERT INTO @LIST VALUES ('Do not show', '2012/02/01 00:00:00', '2012/02/28 23:59:59.997');
    		INSERT INTO @LIST VALUES ('Do not show', '2012/02/01 00:00:01', '2012/02/29');
    		INSERT INTO @LIST VALUES ('Do not show', '2005/05/01', '2020/05/01');
    		INSERT INTO @LIST VALUES ('Do not show', '2011/02/01', '2012/02/15');
    
    		INSERT INTO @LIST VALUES ('Do not show', '2012/01/31', '2012/03/01');
    		INSERT INTO @LIST VALUES ('Do not show', '2012/01/31', '2012/02/01');
    		INSERT INTO @LIST VALUES ('Do not show', '2012/01/31', '2012/02/01 15:00:00');
    		INSERT INTO @LIST VALUES ('Do not show', '2012/01/31', '2012/02/28 23:59:59.997');
    		INSERT INTO @LIST VALUES ('Do not show', '2012/01/31', '2012/03/01');
    
    		INSERT INTO @LIST VALUES ('Do not show', '2012/02/01', '2012/02/15');
    		INSERT INTO @LIST VALUES ('Do not show', '2012/02/01', '2012/02/28 23:59:59.997');
    		INSERT INTO @LIST VALUES ('Do not show', '2012/02/01', '2012/03/01');
    
    		INSERT INTO @LIST VALUES ('Do not show', '2012/02/02', '2012/02/15');
    		INSERT INTO @LIST VALUES ('Do not show', '2012/02/02', '2012/02/28 23:59:59.997');
    		INSERT INTO @LIST VALUES ('Do not show', '2012/02/02', '2012/03/01');
    
    		INSERT INTO @LIST VALUES ('Do not show', '2012/02/28 23:59:59.997', '2012/03/02');
    
    Optional_List_the_table:
    		Select 'The entire list', * from @LIST		-- Just to liest it all out, make life simpler.
    
    
    Declare_and_set_criteria_borrowed_and_rewritten_for_SQL2005_compatibility:
    		DECLARE @StartDate datetime
    		Set @startdate = '2012-02-01 00:00:00.000';
    		DECLARE @EndDate datetime
    		Set @enddate = '2012-02-28 23:59:59.997';
    
    
    
    JohnQFloridas_Way:
    		Select * from @LIST 
    		EXCEPT		-- A "make life easier" method: Select all and use an EXCEPT against a "positive comparison" search, rather than a single query with compound "not" comparisons
    		Select * from @List 
    		 where  ( (startdate between @startdate and @enddate) or (enddate between @startdate and @enddate ) )
    			or  ( startdate < @startdate and enddate > @enddate )
    		order by testcaseorder


    Ok, fine... (editing)... Here's a more traditional way, minus the EXCEPT...

    JohnQFloridas_Way_without_except:
    		Select * from @List 
    		 where Not
    			( 
    				( (startdate between @startdate and @enddate) or (enddate between @startdate and @enddate ) )
    				or
    				( startdate < @startdate and enddate > @enddate )
    			)
    		order by testcaseorder

    • Изменено johnqflorida 27 февраля 2012 г. 15:52 added 2nd way
    • Помечено в качестве ответа Kalman TothModerator 5 марта 2012 г. 15:05
    27 февраля 2012 г. 15:49
  • If you need intervals, that has at least one common time point with (@start_date_time @end_date_time) and at the same time at least one different time point, then

    where (startdate between @startdate and @enddate) and enddate > @enddate
         or (enddate between @startdate and @enddate) and startdate < @startdate 


    Serg

    • Помечено в качестве ответа Kalman TothModerator 5 марта 2012 г. 15:06
    28 февраля 2012 г. 14:10

Все ответы

  • You  mean that dates comes in between your parameters


     i.e   my @startDate = 02/01/2012 and @endDate= 02/29 /2012.
     
     should come in ordered way 

    27 февраля 2012 г. 13:32
  • declare @t table(StartDate date,EndDate date)
    insert into @t
    select '2000-02-01'  ,  '2000-02-15'     union all
    select '2014-02-01'  ,  '2015-02-28'    union all
    select '2012-02-01' ,   '2012-02-29'   union all
    select '2005-05-01' ,   '2020-05-01'   union all
    select '2011-02-01' ,   '2012-02-29'   union all
    select '2012-01-31' ,   '2012-02-15'


     declare @startDate date= '02/01/2012' , @endDate date= '02/29/2012'

    select * from @t where DATEDIFF(DAY,StartDate,EndDate)<>DATEDIFF(DAY,@startDate,@endDate)

    Thanks and regards, Rishabh , Microsoft Community Contributor

    27 февраля 2012 г. 13:33
  • where

    StartDate >@endDate  or @startDate > EndDate

    P.S.

    (01/31/2012 -   02/15/2012)  overlaps (02/01/2012 - 02/29 /2012) =(02/01/2012 - 02/15/2012).


    Serg

    27 февраля 2012 г. 13:42
  • Hi Rishabh,

    What if I have the same difference between two dates but in different years?

    Sammy,

    Try this code:

    USE [tempdb]; GO CREATE TABLE [DateRanges] ( [StartDate] date, [EndDate] date ); GO INSERT INTO [DateRanges] VALUES ('20000201', '20000215'); INSERT INTO [DateRanges] VALUES ('20140201', '20150228'); --no 29th of Febr in 2015. it is not a leap year. INSERT INTO [DateRanges] VALUES ('20120201', '20120229'); INSERT INTO [DateRanges] VALUES ('20050501', '20200501'); INSERT INTO [DateRanges] VALUES ('20110201', '20120215'); INSERT INTO [DateRanges] VALUES ('20120131', '20120215'); GO DECLARE @StartDate date = '20120201'; DECLARE @EndDate date = '20120229'; SELECT * FROM [DateRanges] WHERE ([StartDate] < @StartDate AND [EndDate] < @EndDate) OR ([StartDate] > @StartDate AND [EndDate] > @EndDate) OR ([StartDate] < @StartDate AND [EndDate] > @EndDate)


    I hope it helps.

    Janos


    There are 10 type of people. Those who understand binary and those who do not.
    My Blog


    • Изменено Janos Berke 27 февраля 2012 г. 13:50 fixed the where statement
    • Помечено в качестве ответа Kalman TothModerator 5 марта 2012 г. 15:04
    27 февраля 2012 г. 13:47
  • Sorry I posted It in a hurry.
     


    Thanks and regards, Rishabh , Microsoft Community Contributor

    27 февраля 2012 г. 14:03
  • Hi Rishabh,

    that is fine ;) I just wanted to highlight  that all codes must be tested by the original requester as we may post codes w/o tested or w bugs.

    Janos


    There are 10 type of people. Those who understand binary and those who do not.
    My Blog

    27 февраля 2012 г. 14:06
  • Hi,

    As per your sample 01/31/2012 - 02/15/2012 overlaps your date range, but you have that in your output too. is that correct?

    Please check if the below query helps.

    DECLARE @StartDate date = '20120201';

    DECLARE @EndDate date = '20120229';

    SELECT  * FROM  [DateRanges]

    WHERE ( [EndDate]  < @StartDate)

    OR

    ([StartDate] > @EndDate )

    Please check and let us know if this helps.

    Irshad


    Please click "Mark as Answer" if this resolves your problem or "Vote as Helpful" if you find it helpful.

    BH

    27 февраля 2012 г. 14:13
  • @Janos,

    this also seems to be out of range as per my understanding

    INSERT INTO [DateRanges] VALUES( '2012-02-01', '2013-03-01'    )

    May be this will do in your code:

    SELECT
        *
    FROM
        [DateRanges]
    WHERE
        ([StartDate] < @StartDate AND [EndDate]  < @EndDate)
    OR
        ([StartDate] > @StartDate AND [EndDate] > @EndDate)
    OR
        ([StartDate] <= @StartDate AND [EndDate] > @EndDate)
        OR
         ([StartDate] < @StartDate AND [EndDate] >= @EndDate)


    Thanks and regards, Rishabh , Microsoft Community Contributor

    27 февраля 2012 г. 14:16
  • select * from myTable 
    where StartDate < @EndDate
    UNION ALL
    select * from myTable where EndDate < @StartDate

    These records don't overlap the passed date range. The above assumes that both StartDate and EndDate are always filled and the StartDate is always less than EndDate in a table (no bad records).

    In your sample, that last range partially overlaps the passed range. Do you want to show records with partial overlap and only exclude complete overlaps? 


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog



    27 февраля 2012 г. 14:24
  • @Rishabh, It is overlapping in my code (just removed one line from the WHER statement :) ) . I think the original requester may decide what he/she meant on overlapping. I used this logic:


    There are 10 type of people. Those who understand binary and those who do not.
    My Blog

    27 февраля 2012 г. 14:26
  • As you probably discovered as you started writing your query, it's a bit more complex than meets the eye:  Here are all the conditions (business rules, not necessarily your actual SQL comparisons) you'd need to account for:  (fabricate them all in your test data, at the very least, to make sure whatever algorithm you use accomodates them all).

    1. Compare range starts before and ends after your @start/@end
    2. Compare range starts before, ends at the exact start time of your @start/@end
    3. Compare range starts before, ends sometime within your @start/@end
    4. Compare range starts before, ends at the exact end time of your @start/end
    5. Compare range starts at the exact start time, ends before your @start/@end
    6. Compare range starts at the exact start, ends at the exact end of your @start/@end
    7. Compare range starts at the exact start, ends after the end of your @start/@end
    8. Compare range starts within (after start time) and ends within (before end time) of your @start/@end
    9. Compare range starts within (after start time) and ends at exact end time of your @start/@end
    10. Compare range starts within (after start time) and ends after your @start/@end
    11. Compare range starts at the exact same time as the end of your @start/@end and ends afterwards.

    It's very easy to treat this as a more simple problem than it actually is.  Start Anding and Oring and cover all your bases. 

    Scenario number 1 is probably the second most often overlooked scenario by most people.

    And the most often overlooked scenario is not accommodating for the END of the day for your end date range...   For example, simply specifying '2/29/2012' as your end date in a comparison operator means you are specifying '2/29/2012 00:00:00.000' unless you specify otherwise.  So, if you were to compare that against a value that fell on your end date, and if that value had a time component (2/29/2012 at 3 p.m., for example) then your comparison would be incorrect.   I suggest adding a day, and doing a "less than 3/1/2012 00:00:00.000", rather than a less-than-or-equal '2-29-2012 23:59:59.997'



    • Изменено johnqflorida 27 февраля 2012 г. 14:34 fix typos
    • Помечено в качестве ответа Kalman TothModerator 5 марта 2012 г. 15:04
    27 февраля 2012 г. 14:29
  • @johnqflorida,

    you may be right, but let the original requester explain what he/she meant on "overlapping". Your first rule is missing in my drawing as well as my where statement. As i said, codes/solutions may not be tested and provided AS IS, without warranty :)

    Janos


    There are 10 type of people. Those who understand binary and those who do not.
    My Blog

    27 февраля 2012 г. 14:35
  • Dear All,

    Here is my data:

    Set @start_date_time='2010-02-01 02:00:00.000'
    Set @end_date_time= '2099-12-31 23:59:00.000'

    Below is my table:

    2011-02-01 02:00:00.000 2099-12-31 23:59:00.000
    2011-02-01 02:00:00.000 2099-12-31 23:59:00.000
    1990-02-01 02:00:00.000 2099-12-31 23:59:00.000
    2011-02-01 02:00:00.000 2099-12-31 23:59:00.000
    2011-02-01 02:00:00.000 2099-12-31 23:59:00.000

    So the query should display:

    2011-02-01 02:00:00.000 2099-12-31 23:59:00.000
    2011-02-01 02:00:00.000 2099-12-31 23:59:00.000
    2011-02-01 02:00:00.000 2099-12-31 23:59:00.000
    2011-02-01 02:00:00.000 2099-12-31 23:59:00.000

    it should not display  (1990-02-01 02:00:00.000 -2099-12-31 23:59:00.000) since my @start_date_time/@end_date_time overlaps it

    I used the query of Janos Berke but it didn't show anything.

    cheers,

    Sammy

    27 февраля 2012 г. 14:37
  • Using your definition of overlap, it will be

    select * from myTable T where not (StartDate < @StartDate and EndDate <=@EndDate)


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    27 февраля 2012 г. 14:42
  • Hi,

    Based on the new reqs, plese check this script:

    USE [tempdb];
    GO
    CREATE TABLE [DateRanges]
    (
    	[StartDate] datetime,
    	[EndDate]	datetime
    );
    GO
     
     
    INSERT INTO [DateRanges] VALUES ('2011-02-01 02:00:00.000', '2099-12-31 23:59:00.000');
    INSERT INTO [DateRanges] VALUES ('2011-02-01 02:00:00.000', '2099-12-31 23:59:00.000'); 
    INSERT INTO [DateRanges] VALUES ('1990-02-01 02:00:00.000', '2099-12-31 23:59:00.000');
    INSERT INTO [DateRanges] VALUES ('2011-02-01 02:00:00.000', '2099-12-31 23:59:00.000');
    INSERT INTO [DateRanges] VALUES ('2011-02-01 02:00:00.000', '2099-12-31 23:59:00.000');
    GO
    DECLARE @StartDate datetime = '2010-02-01 02:00:00.000';
    DECLARE @EndDate datetime = '2099-12-31 23:59:00.000';
    SELECT 
    	* 
    FROM 
    	[DateRanges]
    WHERE
    	([StartDate] >= @StartDate AND [EndDate] <= @EndDate)

    I hope it helps.

    J.


    There are 10 type of people. Those who understand binary and those who do not.
    My Blog

    • Помечено в качестве ответа Kalman TothModerator 5 марта 2012 г. 15:04
    27 февраля 2012 г. 14:44
  • Janos,

    Thanks your reply. I tried your code with the following data:

    Set @start_date_time='2012-02-01 02:00:00.000'
    Set @end_date_time= '2099-12-31 02:00:00.000'

    My table data is:

    2011-02-01 02:00:00.000 2099-12-31 23:59:00.000
    2011-02-01 02:00:00.000 2099-12-31 23:59:00.000
    1990-02-01 02:00:00.000 2011-12-31 23:59:00.000
    2011-02-01 02:00:00.000 2099-12-31 23:59:00.000
    2011-02-01 02:00:00.000 2099-12-31 23:59:00.000

    When I run your query it doesn't display anything. Where as  it should display  (1990-02-01 02:00:00.000  - 2011-12-31 23:59:00.000) because my @start_date_time/@end_date_time does not entirely cover this date .

    Please help me in this case.

    cheers,
    Sammy

     
    27 февраля 2012 г. 15:10
  • Hi Sammy,

    In this case you can use this query:

    SELECT 
    	* 
    FROM 
    	[DateRanges]
    WHERE
    	([StartDate] <= @StartDate AND [EndDate] >= @EndDate)

    Janos


    There are 10 type of people. Those who understand binary and those who do not.
    My Blog

    27 февраля 2012 г. 15:27
  • To cover this case, use this criterion:

    DECLARE @StartDate datetime = '2012-02-01 02:00:00.000';
    DECLARE @EndDate datetime = '2099-12-31 23:59:00.000';
    
    select * from [DateRanges] 
    where EndDate <@EndDate and StartDate <@StartDate 


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    • Помечено в качестве ответа Kalman TothModerator 5 марта 2012 г. 15:05
    27 февраля 2012 г. 15:28
  • Ok, I've officially spent too much "fun time" in the forums today:  :-)  

    Here's two things:  Part 1, populates all possible scenarios, so as you experiment with other approaches if you choose to do that, you have a definitive table with all the possibilities included. 

    Part 2, my approach, which I believe has caught it all.  Note that I use a somewhat non-traditional approach at the end (the EXCEPT statement), simply to avoid having to use 'negated' comparisons.  Sometimes, you compare developer time, ease of maintainability for the life cycle of the query, and if you can waste a CPU tick or two, you can opt to make life easier for yourself.

    Create_Temp_table:
    		Declare @LIST TABLE 
    		(
    			TestCaseOrder int identity primary key,  -- Just to keep results in same order as test cases
    			ExpectedResult varchar(99),				 -- Just to make it easier to interpret results
    			[StartDate] datetime,
    			[EndDate]	datetime
    		);
     
    Populate_all_test_scenarios: 
    		INSERT INTO @LIST VALUES ('Should show', '2000/02/01', '2000/02/15');  -- starts/ends before
    		INSERT INTO @LIST VALUES ('Should show', '2014/02/01', '2015/02/28');  -- starts/ends after
    		INSERT INTO @LIST VALUES ('Should show', '2012/01/30', '2012/01/31');  -- starts/ends before
    		INSERT INTO @LIST VALUES ('Should show', '2012/03/01', '2012/03/02');  -- starts/ends after
    
    		INSERT INTO @LIST VALUES ('Do not show', '2012/02/01 00:00:00', '2012/02/29');
    		INSERT INTO @LIST VALUES ('Do not show', '2012/02/01 00:00:00', '2012/02/28 23:59:59.997');
    		INSERT INTO @LIST VALUES ('Do not show', '2012/02/01 00:00:01', '2012/02/29');
    		INSERT INTO @LIST VALUES ('Do not show', '2005/05/01', '2020/05/01');
    		INSERT INTO @LIST VALUES ('Do not show', '2011/02/01', '2012/02/15');
    
    		INSERT INTO @LIST VALUES ('Do not show', '2012/01/31', '2012/03/01');
    		INSERT INTO @LIST VALUES ('Do not show', '2012/01/31', '2012/02/01');
    		INSERT INTO @LIST VALUES ('Do not show', '2012/01/31', '2012/02/01 15:00:00');
    		INSERT INTO @LIST VALUES ('Do not show', '2012/01/31', '2012/02/28 23:59:59.997');
    		INSERT INTO @LIST VALUES ('Do not show', '2012/01/31', '2012/03/01');
    
    		INSERT INTO @LIST VALUES ('Do not show', '2012/02/01', '2012/02/15');
    		INSERT INTO @LIST VALUES ('Do not show', '2012/02/01', '2012/02/28 23:59:59.997');
    		INSERT INTO @LIST VALUES ('Do not show', '2012/02/01', '2012/03/01');
    
    		INSERT INTO @LIST VALUES ('Do not show', '2012/02/02', '2012/02/15');
    		INSERT INTO @LIST VALUES ('Do not show', '2012/02/02', '2012/02/28 23:59:59.997');
    		INSERT INTO @LIST VALUES ('Do not show', '2012/02/02', '2012/03/01');
    
    		INSERT INTO @LIST VALUES ('Do not show', '2012/02/28 23:59:59.997', '2012/03/02');
    
    Optional_List_the_table:
    		Select 'The entire list', * from @LIST		-- Just to liest it all out, make life simpler.
    
    
    Declare_and_set_criteria_borrowed_and_rewritten_for_SQL2005_compatibility:
    		DECLARE @StartDate datetime
    		Set @startdate = '2012-02-01 00:00:00.000';
    		DECLARE @EndDate datetime
    		Set @enddate = '2012-02-28 23:59:59.997';
    
    
    
    JohnQFloridas_Way:
    		Select * from @LIST 
    		EXCEPT		-- A "make life easier" method: Select all and use an EXCEPT against a "positive comparison" search, rather than a single query with compound "not" comparisons
    		Select * from @List 
    		 where  ( (startdate between @startdate and @enddate) or (enddate between @startdate and @enddate ) )
    			or  ( startdate < @startdate and enddate > @enddate )
    		order by testcaseorder


    Ok, fine... (editing)... Here's a more traditional way, minus the EXCEPT...

    JohnQFloridas_Way_without_except:
    		Select * from @List 
    		 where Not
    			( 
    				( (startdate between @startdate and @enddate) or (enddate between @startdate and @enddate ) )
    				or
    				( startdate < @startdate and enddate > @enddate )
    			)
    		order by testcaseorder

    • Изменено johnqflorida 27 февраля 2012 г. 15:52 added 2nd way
    • Помечено в качестве ответа Kalman TothModerator 5 марта 2012 г. 15:05
    27 февраля 2012 г. 15:49
  • Dear johnqflorida,

    Thanks your reply. I tried your code with the following data:

    DECLARE @start_date_time datetime ='1999-01-30 02:00:00.000'
    DECLARE @end_date_time datetime= '2000-01-30 02:00:00.000'

    My table consists of the following data:

    2000-01-30 02:00:00.000 2020-04-21 02:00:00.000

    When I run your query. I don't get anything in the table. In the query result it should display 2000-01-30 02:00:00.000 - 2020-04-21 02:00:00.000 because my @start_date_time and @end_date_time does not entirely cover this date.

    cheers,
    Sammy

    28 февраля 2012 г. 9:29
  • @Sammy:

    Can you restate your problem in this term:

    http://stackoverflow.com/questions/325933/determine-whether-two-date-ranges-overlap

    so we could underatand  "not entirely cover" exactly as you need.


    Serg

    28 февраля 2012 г. 11:04
  • Dear All,

    Let me explain what I mean date overlapping  Lets say I have 2 dates 

    DECLARE @start_date_time datetime ='1999-01-30 02:00:00.000'
    DECLARE @end_date_time datetime= '2000-01-30 02:00:00.000'

    Invalid Dates:
    2001-01-30 02:00:00.000 - 2005-01-30 02:00:00.000
    1990-01-30 02:00:00.000 - 1995-01-30 02:00:00.000
    2001-02-30 02:00:00.0002001-03-30 02:00:00.000
    1999-02-01 02:00:00.000 - 2000-01-30 02:00:00.000
    2000-01-30 02:00:00.000 - 2003-01-30 02:00:00.000

    Valid Dates:

    1999-05-30 02:00:00.000 - 1999-08-30 02:00:00.000
    1999-01-30 02:00:00.000 - 2000-01-30 02:00:00.000
    1980-01-30 02:00:00.000 - 2012-01-30 02:00:00.000
    2000-01-12 02:00:00.000 - 2000-01-18 02:00:00.000

    I want the list of all the invalid dates.

    Please let me know if you need any further information.

    cheers,

    Sammy

    28 февраля 2012 г. 11:53
  • Sammy,

    Please find the code for the valid dates below:

    USE [tempdb];
    GO
    CREATE TABLE [DateRanges]
    (
    	[StartDate] datetime,
    	[EndDate]	datetime
    );
    GO
     
     
    INSERT INTO [DateRanges] VALUES ('2001-01-30 02:00:00.000', '2005-01-30 02:00:00.000');
    INSERT INTO [DateRanges] VALUES ('1990-01-30 02:00:00.000', '1995-01-30 02:00:00.000');
    INSERT INTO [DateRanges] VALUES ('2001-02-28 02:00:00.000', '2001-03-30 02:00:00.000');
    INSERT INTO [DateRanges] VALUES ('1999-02-01 02:00:00.000', '2000-01-30 02:00:00.000');
    INSERT INTO [DateRanges] VALUES ('2000-01-30 02:00:00.000', '2003-01-30 02:00:00.000');
    INSERT INTO [DateRanges] VALUES ('1999-05-30 02:00:00.000', '1999-08-30 02:00:00.000');
    INSERT INTO [DateRanges] VALUES ('1999-01-30 02:00:00.000', '2000-01-30 02:00:00.000');
    INSERT INTO [DateRanges] VALUES ('1980-01-30 02:00:00.000', '2012-01-30 02:00:00.000');
    INSERT INTO [DateRanges] VALUES ('2000-01-12 02:00:00.000', '2000-01-18 02:00:00.000');
    GO
    DECLARE @StartDate datetime = '1999-01-30 02:00:00.000';
    DECLARE @EndDate datetime = '2000-01-30 02:00:00.000';
    SELECT 
    	* 
    FROM 
    	[DateRanges]
    WHERE
    	([StartDate] <= @StartDate AND [EndDate] >= @EndDate)
    OR
    	([StartDate] > @StartDate AND [EndDate] < @EndDate)

    I hope it helps.

    Janos


    There are 10 type of people. Those who understand binary and those who do not.
    My Blog

    28 февраля 2012 г. 12:09
  • It looks as if you're saying, "If a date ENDS at the exact time that my @start time starts at, I don't consider that an overlap".

    So add 3 milliseconds to the @Start date/time, so that there's not an overlap.  Or change the "between" statement to a compound greater than and less than.

    This was added.

    			Set @StartDate = Dateadd(ms, 3, @Startdate)

    For this... (I removed the EXCEPT version, and simplified the final query a bit by removing the parentheses, which weren't necessary). 

    	Create_Temp_table:
    			Declare @LIST TABLE 
    			(
    				TestCaseOrder int identity primary key,  -- Just to keep results in same order as test cases
    				ExpectedResult varchar(99),				 -- Just to make it easier to interpret results
    				[StartDate] datetime,
    				[EndDate]	datetime
    			);
    	 
    	Populate_all_test_scenarios: 
    			INSERT INTO @LIST VALUES ('Should show', '2000/02/01', '2000/02/15');  -- starts/ends long before
    			INSERT INTO @LIST VALUES ('Should show', '2014/02/01', '2015/02/28');  -- starts/ends long after
    			INSERT INTO @LIST VALUES ('Should show', '2012/01/30', '2012/01/31');  -- starts/ends just before
    			INSERT INTO @LIST VALUES ('Should show', '2012/03/01', '2012/03/02');  -- starts/ends just after
    			INSERT INTO @LIST VALUES ('Should show (end time = start time not an overlap per Sammy', '2012/01/31', '2012/02/01'); -- starts before, ends at exact start
    
    			INSERT INTO @LIST VALUES ('Do not show', '2012/02/01 00:00:00', '2012/02/29'); --dates within range
    			INSERT INTO @LIST VALUES ('Do not show', '2012/02/01 00:00:00', '2012/02/28 23:59:59.997'); -- within
    			INSERT INTO @LIST VALUES ('Do not show', '2012/02/01 00:00:01', '2012/02/29'); -- within
    			INSERT INTO @LIST VALUES ('Do not show', '2005/05/01', '2020/05/01'); -- starts way before and ends way after
    			INSERT INTO @LIST VALUES ('Do not show', '2011/02/01', '2012/02/15'); -- starts way before, ends within
    
    			INSERT INTO @LIST VALUES ('Do not show', '2012/01/31', '2012/03/01'); -- starts before and ends after
    			INSERT INTO @LIST VALUES ('Do not show', '2012/01/31', '2012/02/01 15:00:00'); -- starts before, ends within
    			INSERT INTO @LIST VALUES ('Do not show', '2012/01/31', '2012/02/28 23:59:59.997'); -- starts before, ends at exact end
    			INSERT INTO @LIST VALUES ('Do not show', '2012/01/31', '2012/03/01'); -- starts before and ends after
    
    			INSERT INTO @LIST VALUES ('Do not show', '2012/02/01', '2012/02/15'); -- starts at exact start, ends within
    			INSERT INTO @LIST VALUES ('Do not show', '2012/02/01', '2012/02/28 23:59:59.997'); -- start and end, exact match
    			INSERT INTO @LIST VALUES ('Do not show', '2012/02/01', '2012/03/01'); -- starts at exact start, ends after
    
    			INSERT INTO @LIST VALUES ('Do not show', '2012/02/02', '2012/02/15'); -- starts within, ends within
    			INSERT INTO @LIST VALUES ('Do not show', '2012/02/02', '2012/02/28 23:59:59.997'); -- starts within, ends at exact end
    			INSERT INTO @LIST VALUES ('Do not show', '2012/02/02', '2012/03/01'); -- starts within, ends after
    
    			INSERT INTO @LIST VALUES ('Do not show', '2012/02/28 23:59:59.997', '2012/03/02');  -- starts at exact end date
    
    	Optional_List_the_table:
    			Select 'The entire list', * from @LIST		-- Just to list it all out, make life simpler.
    
    
    	Declare_and_set_criteria_borrowed_and_rewritten_for_SQL2005_compatibility:
    			DECLARE @StartDate datetime
    			Set @startdate = '2012-02-01 00:00:00.000';
    			Set @StartDate = Dateadd(ms, 3, @Startdate)
    			DECLARE @EndDate datetime
    			Set @enddate = '2012-02-28 23:59:59.997';
    
    
    
    	JohnQFloridas_Way_without_except2:
    			Select 'JohnQFlorida', * from @List 
    			 where Not
    				( 
    					startdate between @startdate and @enddate
    					or enddate between @startdate and @enddate
    					or startdate < @startdate and enddate > @enddate 
    				)
    			order by testcaseorder
    


    • Изменено johnqflorida 28 февраля 2012 г. 13:12
    28 февраля 2012 г. 12:46
  • Dear All,

    Let me explain what I mean date overlapping  Lets say I have 2 dates 

    DECLARE @start_date_time datetime ='1999-01-30 02:00:00.000'
    DECLARE @end_date_time datetime= '2000-01-30 02:00:00.000'

    Invalid Dates:
    1999-02-01 02:00:00.000 - 2000-01-30 02:00:00.000

    Valid Dates:
    1999-01-30 02:00:00.000 - 2000-01-30 02:00:00.000

    Why? both are within (@start_date_time  @end_date_time) i.e. satisfy

    StartDate >= @start_date_time and  EndDate <= @end_date_time

    the only difference is StartDate = @start_date_time (valid)  or  StartDate > @start_date_time  (invalid) 

    Explain please what is the difference when comparing to @start_date_time   and when comparing to  @end_date_time.


    Serg

    28 февраля 2012 г. 13:44
  • If you need intervals, that has at least one common time point with (@start_date_time @end_date_time) and at the same time at least one different time point, then

    where (startdate between @startdate and @enddate) and enddate > @enddate
         or (enddate between @startdate and @enddate) and startdate < @startdate 


    Serg

    • Помечено в качестве ответа Kalman TothModerator 5 марта 2012 г. 15:06
    28 февраля 2012 г. 14:10
  • Allen's Interval Algebra is a very interesting article for dealing with intervals (date ranges). It defines 13 distinct, exhaustive relationships between two intervals. 9 out of 13 relationships define some kind of overlap (full or partial). Checking for these 9 possible cases is fairly straight forward and requires six <= conditions. However as described on this article, it is sufficient to check two conditions for an overlap:

    -- @d1 and @d2 are user supplied dates
    -- start_date and end_date are datetime columns
    SELECT * FROM <table> WHERE @d2 > start_date AND end_date > @d1

    The query is fairly straight forward. Check whether the date is "outside" the start_date — end_date pair using this query:

    SELECT * FROM WHERE @d2 <= start_date OR end_date <= @d1

    To check for a full/partial overlap, perform a NOT on the result. Something that is not fully outside is fully/partially inside. I believe this query should be pretty fast.


    Salman - http://salman-w.blogspot.com/





    • Изменено Salman A 10 марта 2013 г. 17:33
    10 июня 2012 г. 14:13