SQL select where month/year are within 2 dates

Osama Gadour 0
Viewed 37

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)
select sql
2 Answers

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

EDIT: switched the < and > to the correct places.

ClumZZZey0

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
Kevin Su0
Related