SQL select where month/year are within 2 dates-CodePudding

I have the following table:

id	StartDate	EndDate
1	01/03/2021	24/09/2022
2	11/06/2021	19/12/2022
3	17/09/2021	22/03/2022
4	21/05/2021	30/05/2022

and I have 2 inputs, month and year, what I need is to get the dates where the selected month/year is within the start and end date, for example, the input is 07/2022, the results I need are:

id	StartDate	EndDate
1	01/03/2021	24/09/2022
2	11/06/2021	19/12/2022

what I tried:

select 
  * 
from 
  contracts 
where 
    (year(startdate) >= @year 
    and 
    month(startdate) >=@month)  
  and 
    (year(enddate) <= @year 
    and
    month(enddate) <=@month)

CodePudding user response：

ClumZZZey's approach is correct. Seems like there is a small mistake on the query. Try this instead.

SELECT * 
FROM contracts
WHERE (year(startdate) * 100   month(startdate)) <= @year * 100   @month 
  AND (year(enddate) * 100    month(enddate)) >= @year * 100   @month

CodePudding user response：

Decoupling the month and the year makes it tricky. You want to keep the together. For example

SELECT * 
FROM contracts
WHERE (year(startdate) * 100   month(startdate)) >= @year * 100   @month 
AND (year(enddate) * 100    month(enddate)) <= @year * 100   @month