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
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)