Compare date Range by Month (Integer) and Year (Integer)

Question

I'm having a problem comparing the date range. I have to validate dates that are within a certain month and year. The month and year are integer values.

NOTE: I´m using OUTSYSTEMS aggregates using Oracle DataBase

Example for two results of a query:

    Start Date    End Date 
1   2020-08-16    2020-10-14
2   2019-11-01    2020-08-15

Case 1

Input:

Month = 9
Year = 2020

Expected Result:

    Start Date    End Date 
1   2020-08-16    2020-10-14

Case 2

Input:

Month = 8
Year = 2020

Expected Result:

    Start Date    End Date 
1   2020-08-16    2020-10-14
2   2019-11-01    2020-08-15

Case 3

Input:

Month = 3
Year = 2020

Expected Result:

    Start Date    End Date 
2   2019-11-01    2020-08-15

Case 4

Input:

Month = 10
Year = 2019

Expected Result: No Row

The selection is in Java Way. I´m using a system function like Month() and Year() to convert the rows to the integers.

Like this

((Month(StartDate) <= Month and Month(EndDate) = Month)
and
(Year(StartDate) <= Year and Year(EndDate) = Year))
or
((Month(StartDate) <= Month and Month(EndDate) = Month)
and
(Year(StartDate) <= Year and Year(EndDate) = Year))

The code above won't work. I try many combinations without success. I have no special comparison functions. For my analysis, I have four scenarios to create to bring the dates that are included in the month and year that I am researching. But I'm not getting the code to work. Someone can light the way for me

Answer 1

A simple approach uses arithmetics:

where year * 100 + month 
    between year(startdate) * 100 + month(startdate)
        and year(enddate)   * 100 + month(enddate)

However this probably isn't the most efficient method. In general, you want to avoid applying functions on the column you filter on. A better alternative woul be to convert the year/month parameter to a date - unfortunately you did not tag your database, and date functions are highly vendor-specific, so it is not really possible to suggest.

If you don't want between:

where year * 100 + month >= year(startdate) * 100 + month(startdate)
  and year * 100 + month <= year(enddate)   * 100 + month(enddate)

Answer 2

Does this work? Considering your inputs m for month and y for year:

StartDate <= AddDays(AddMonths(NewDate(Year(y), Month(m), 1),1)-1)
and
EndDate >= NewDate(Year(y), Month(m), 1))

The thinking is like: filter by all start dates that are lower than the last day of input month and all the end dates that are greater than the first day of input month.

Regarding performance, with this approach you don't have to do any logic/filter on the columns you're filtering on.

Answer 3

The vendor-independent solution

The answer by GMB is nice, I might go with it if it were me. As GMB says, it is vendor specific because the date functions are. If you want a solution that works across database vendors, do the date math in Java so you only need simple date comparisons in the database.

    int month = 8;
    int year = 2020;

    YearMonth ym = YearMonth.of(year, month);
    LocalDate monthStart = ym.atDay(1);
    LocalDate monthEnd = ym.atEndOfMonth();

When you pass these dates to your query, your search condition may be put simply:

  where startDate <= monthEnd and endDate >= monthStart