войти
 
ГлавнаяНовостиБиблиотекаОбучениеСкачатьМероприятияСообществоФорумыПоддержкаО проекте
Ресурсы для ИТ-профессионалов >
> 
> 
Query for Overlapping Dates

Answered Query for Overlapping Dates

  • 27 февраля 2012 г. 13:16
     
     
    Нужно войти
    0

    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:32
     
     
    Нужно войти
    0

    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:33
     
     
    Нужно войти
    0
    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:42
     
     
    Нужно войти
    1

    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:47
     
     Отвечено С кодом
    Нужно войти
    0

    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


     
  • 27 февраля 2012 г. 14:03
     
     
    Нужно войти
    0

    Sorry I posted It in a hurry.
     

    Thanks and regards, Rishabh , Microsoft Community Contributor

     
  • 27 февраля 2012 г. 14:06
     
     
    Нужно войти
    0

    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:13
     
      С кодом
    Нужно войти
    0

    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:16
     
     
    Нужно войти
    0

    @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:24
    Модератор
     
      С кодом
    Нужно войти
    0
     

    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:26
     
     
    Нужно войти
    0

    @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:29
     
     Отвечено
    Нужно войти
    0

    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'



     
  • 27 февраля 2012 г. 14:35
     
     
    Нужно войти
    0

    @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:37
     
     
    Нужно войти
    0

    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:42
    Модератор
     
     
    Нужно войти
    0

    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:44
     
     Отвечено С кодом
    Нужно войти
    0

    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

     
  • 27 февраля 2012 г. 15:10
     
     
    Нужно войти
    0

    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:27
     
      С кодом
    Нужно войти
    0

    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:28
    Модератор
     
     Отвечено С кодом
    Нужно войти
    0

    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

     
  • 27 февраля 2012 г. 15:49
     
     Отвечено С кодом
    Нужно войти
    0

    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

     
  • 28 февраля 2012 г. 9:29
     
     
    Нужно войти
    0

    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 г. 11:04
     
     
    Нужно войти
    0

    @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:53
     
     
    Нужно войти
    0

    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.000 - 2001-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 г. 12:09
     
      С кодом
    Нужно войти
    0

    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:46
     
      С кодом
    Нужно войти
    0

    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 г. 13:44
     
     
    Нужно войти
    0

    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 г. 14:10
     
     Отвечено
    Нужно войти
    0

    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

     
  • 10 июня 2012 г. 14:13
     
      С кодом
    Нужно войти
    0

    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 июня 2012 г. 14:13 typo
    • Изменено Salman A 10 июня 2012 г. 15:01
    • Изменено Salman A 10 июня 2012 г. 15:04
    • Изменено Salman A 10 июня 2012 г. 15:07
    • Изменено Salman A 10 марта 2013 г. 17:33
    •