英文:
Compare date Range by Month (Integer) and Year (Integer)
问题
我在比较日期范围时遇到了问题。我必须验证在特定月份和年份内的日期。月份和年份是整数值。
注意:我正在使用OUTSYSTEMS聚合,使用的是Oracle数据库。
查询结果的两个示例:
开始日期 结束日期
1 2020-08-16 2020-10-14
2 2019-11-01 2020-08-15
案例 1
输入:
月份 = 9
年份 = 2020
预期结果:
开始日期 结束日期
1 2020-08-16 2020-10-14
案例 2
输入:
月份 = 8
年份 = 2020
预期结果:
开始日期 结束日期
1 2020-08-16 2020-10-14
2 2019-11-01 2020-08-15
案例 3
输入:
月份 = 3
年份 = 2020
预期结果:
开始日期 结束日期
2 2019-11-01 2020-08-15
案例 4
输入:
月份 = 10
年份 = 2019
预期结果:无行
选择是以Java方式进行的。我正在使用类似于Month()和Year()的系统函数将行转换为整数。
像这样:
((Month(开始日期) <= 月份 and Month(结束日期) = 月份)
and
(Year(开始日期) <= 年份 and Year(结束日期) = 年份))
or
((Month(开始日期) <= 月份 and Month(结束日期) = 月份)
and
(Year(开始日期) <= 年份 and Year(结束日期) = 年份))
上面的代码不起作用。我尝试了许多组合都没有成功。我没有特殊的比较函数。根据我的分析,我有四种情景要创建,以获取包含在我正在研究的月份和年份中的日期。但是我无法让代码工作。有人能为我指点迷津吗?
英文:
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
答案1
得分: 3
一个简单的方法使用算术操作:
where year * 100 + month
between year(startdate) * 100 + month(startdate)
and year(enddate) * 100 + month(enddate)
然而,这可能不是最高效的方法。一般来说,您希望避免在过滤列上应用函数。一个更好的替代方法是将年/月参数转换为日期 - 不幸的是,您没有标记您的数据库,日期函数在不同的数据库中会有很大差异,因此很难给出建议。
如果您不想使用 between
:
where year * 100 + month >= year(startdate) * 100 + month(startdate)
and year * 100 + month <= year(enddate) * 100 + month(enddate)
英文:
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)
答案2
得分: 1
以下是翻译好的部分:
考虑到您的输入,其中 m 代表月份,y 代表年份:
起始日期 <= 添加天数(添加月份(新日期(年份(y),月份(m),1),1)-1)
且
结束日期 >= 新日期(年份(y),月份(m),1))
思路是:筛选所有起始日期早于输入月份的最后一天,并且筛选所有结束日期晚于输入月份的第一天。
关于性能,使用这种方法,您无需对正在筛选的列进行任何逻辑/筛选操作。
英文:
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.
答案3
得分: 1
供应商无关的解决方案
GMB提供的答案很好,如果我是您,我可能会选择它。正如GMB所说,这是供应商特定的,因为日期函数是如此。如果您想要一个在各个数据库供应商之间都适用的解决方案,在Java中进行日期计算,这样您只需要在数据库中进行简单的日期比较。
int month = 8;
int year = 2020;
YearMonth ym = YearMonth.of(year, month);
LocalDate monthStart = ym.atDay(1);
LocalDate monthEnd = ym.atEndOfMonth();
当您将这些日期传递给您的查询时,您的搜索条件可以简单地设置为:
where startDate <= monthEnd and endDate >= monthStart
英文:
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
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论