Check if two dates contain this month

Question

My problem is simple ... or maybe not. I have a table containing two dates:

StartDate EndDate 

And I have a constant that is a month. For instance:

 DECLARE @MonthCode AS INT SELECT @MonthCode = 11 /* NOVEMBER */ 

I need SINGLE QUERY to find all records whose StartDate and EndDate includes this month. For instance:

 /* Case 1 */ Aug/10/2009 - Jan/01/2010 /* Case 2 */ Aug/10/2009 - Nov/15/2009 /* Case 3 */ Nov/15/2009 - Jan/01/2010 /* Case 4 */ Nov/15/2009 - Nov/15/2009 /* Case 5 */ Oct/01/2010 - Dec/31/2010 

In the first and last case, special attention is necessary: ​​both dates are outside of November, but cross it.

The following query does not care about cases 1 and 5:

 WHERE MONTH( StartDate ) = @MonthCode OR MONTH( EndDate ) = @MonthCode 

The following query also failed because Aug <Nov & Nov <Jan = false :

 WHERE MONTH( StartDate ) = @MonthCode OR MONTH( EndDate ) = @MonthCode OR ( MONTH( StartDate ) < @MonthCode AND @MonthCode < MONTH( EndDate ) ) 

Answer 1

 DECLARE @MonthCode AS INT SELECT @MonthCode = 11 /* NOVEMBER */ declare @yourtable table( startdate datetime , enddate datetime ) insert into @yourtable( startdate , enddate ) ( select '8/10/2009', '01/01/2010' union all select '8/10/2009' , '11/15/2009' union all select '11/15/2009' , '01/01/2010' union all select '11/15/2009' , '11/15/2009' union all select '10/01/2010' , '12/31/2010' union all select '05/01/2009', '10/30/2009' ) select * from @yourtable where DateDiff(mm, startdate, enddate) > @MonthCode -- can't go over 11 months without crossing date OR (Month(startdate) <= @MonthCode -- before Month selected AND (month(enddate) > =@MonthCode -- after month selected OR year(enddate) > year(startdate) -- or crosses into next year ) ) OR (Month(startdate) >= @MonthCode -- starts after in same year after month and month(enddate) >= @MonthCode -- must end on/after same month assume next year and year(enddate) > year(startdate) ) 

Answer 2

I understand that you are looking for a way to select all the ranges that cross November in any year .

Here is the logic:

• if the range falls by one year (e.g. 2009), the start month must be before or equal to November and the end of the month after or equal to November

• if the range falls on the next two years (for example, 2009-2010), the starting month must be before or equal to November OR at the end of the month or after November

• if the range falls by two years with a difference of more than 1 year (for example, 2008-2010), November is always included in the range (here, in November 2009).

Translated in pseudocode, condition:

 // first case ( (YEAR(StartDate)=YEAR(EndDate)) AND (MONTH(StartDate)<=MonthCode AND MONTH(EndDate)>=MonthCode) ) OR // second case ( (YEAR(EndDate)-YEAR(StartDate)=1) AND (MONTH(StartDate)<=MonthCode OR MONTH(EndDate)>=MonthCode) ) OR // third case ( YEAR(EndDate)-YEAR(StartDate)>1 ) 

Answer 3

Try the following:

  select * from Mytable
 where 
 month (StartDate) = @MonthCode or month (EndDate) = @MonthCode // Nov / 15/2009 - Nov / 15/2009
 or
 dateadd (month, @ MonthCode-1, convert (datetime, convert (varchar, year (StartDate))))
 between StartDate and EndDate // Oct / 01/2010 - Dec / 31/2010
 or
 dateadd (month, @ MonthCode-1, convert (datetime, convert (varchar, year (EndDate))))
 between StartDate and EndDate // Dec / 01/2009 - Dec / 31/2010 - tricky one

The main idea is to check where the 01.November.StartYear and 01.November.EndYear dates are located.

Hope this helps.

Answer 4

Filter for rows that start before the end of the month and end after the start of the month. In October 2009:

 select * from YourTable where StartDate < '2009-11-01' and EndDate >= '2009-10-01' 

Or, just a month as input:

 declare @month datetime set @month = '2009-10-01' select * from YourTable where StartDate < dateadd(month,1,@month) and EndDate >= @month 

Answer 5

There are various functions that you can use to achieve this, such as DATEPART and DATETIFF . However, the real problem is not how to express the state of StartDate or EndDate, which falls on a given month, but how to do it in such a way as to make the request effective. In other words, how to express it with SARGable.

If you are looking for a small change table, something under 10k pages, then that doesn’t make such a big difference, a full scan is probably quite acceptable. The real question is that the table (s) are significant in size and a full scan is unacceptable.

If you do not have an index in any StartDate or EndDate column, it does not matter, the criteria are not searchable, and the query will scan the entire table anyway. However, if there are indexes on StartDate and EndDate, then how you express this condition matters. The critical part of DATETIME indices is that you must express the search as an exact date range. Expressing the condition as a function depending on the DATETIME field will make the condition incurable, which will lead to a full table scan. Thus, this knowledge makes the right way to search for a date range:

 select ... from table where StartDate between '20091101' and '20091201' or EndDate between '20091101' and '20091201'; 

It can also be expressed as:

 select ... from table where StartDate between '20091101' and '20091201' union all select ... from table where EndDate between '20091101' and '20091201' and StartDate not between '20091101' and '20091201'; 

Which query works best depends on several factors, such as the size of the table and the statistics of the actual data in the table.

However, you need the month of November from any year that this request does not give you. The solution to this problem contradicts any instinct that a programmer has: hard code of the corresponding years. In most cases, the tables have a small set of years in any case, something in the range of 4-5 years of past data and plan for 3-4 years more until the system is revised:

 select ... from table where StartDate between '20051101' and '20051201' or EndDate between '20051101' and '20051201' union all select ... from table where StartDate between '20061101' and '20061201' or EndDate between '20061101' and '20061201' union all ... select ... from table where StartDate between '20151101' and '20151201' or EndDate between '20151101' and '20151201'; 

There are 12 months of the year, write 12 separate procedures. Does that sound crazy? That's for sure, but it's the best thing from a SQL query compiler and optimizer. How can such code be supported? 12 separate procedures, with a query that is repeated 10 times (20 times, if you use UNION between StartDate and EndDate to remove OR), 120 repetitions of the code, it should be insensitive. This is actually not the case. Use code generation to create procedures such as XML / XSLT so you can easily modify and save it. Should the client know about 12 procedures and call the appropriate one? Of course not, it calls one wrapper procedure that distinguishes the @Month argument to invoke the correct one.

I believe that anyone who looks at the system after the facts most likely believes that this request was written by a group of drunken monkeys. However, somewhere between parameterization, SARGability indexing, and SQL DATETIME, the fact that this is a modern state when it refers to search calendar intervals arises.

Oh, and if the request falls into the Index Tipping Point , it will still disable the entire argument ...

Update

By the way, there is a cheap way out if you are willing to sacrifice some memory space: two constant calculated columns on StartMonth AS DATEPART(month, StartDate) and EndDate AS DATEPART(month, EndDate) , and the index on each and the query WHERE StartMonth = @Month OR EndMonth = @Month (or again UNION between the two asks for one for “Start one for the end” to remove the OR).

Answer 6

SQL Server 200/2005, you can also do this:

 select * from table where datepart(m,startDate) = 11 and datepart(m,EndDate) = 11 

UPDATE: Deleted and datepart(yyyy,startDate) = datepart(yyyy,endDate) Do you want a specific month regardless of year or day?