给定Oracle中包含日期的字段,您将如何计算周末结束日期使用Sun到星期六作为您的周。 例如,如果日期是2015年1月26日(星期一),则查询应返回2015年1月31日(星期六。如果日期是2015年1月31日,则查询应返回1 / 31/2015
Given a field in Oracle that contains dates, how would you calculate what the week ending date is using Sun thru Sat as your week. For example, if the date is 1/26/2015 (which is a Monday), the query should return 1/31/2015 (which is a Saturday. If the date is 1/31/2015, then the query should return 1/31/2015.
最满意答案
给定任何特定日期/时间值,此表达式将返回前一个星期日的午夜。
TRUNC(whatever_time,'DAY')所以,你可以做这样的事情:
SELECT TRUNC(whatever_time,'DAY') week_starting, TRUNC(whatever_time,'DAY') + 6 week_ending, SUM(sales) FROM table GROUP BY TRUNC(whatever_time,'DAY')你会得到你需要的东西。
请注意, TRUNC(whatever_time,'DAY')名为“NLS_TERRITORY”的Oracle会话初始化参数。 例如,在欧洲,周一被认为是本周的第一个工作日。 尝试这个。
ALTER SESSION SET NLS_TERRITORY=GERMANY; SELECT TRUNC( DATE '2013-12-31', 'DAY'), TRUNC( DATE '2014-01-03', 'DAY') FROM DUAL;完整的文章写在这里: http ://www.plumislandmedia.net/sql-time-processing/using-sql-report-time-intervals-oracle/
Given any particular date / time value, this expression will return midnight of the preceding Sunday.
TRUNC(whatever_time,'DAY')So, you can do stuff like this:
SELECT TRUNC(whatever_time,'DAY') week_starting, TRUNC(whatever_time,'DAY') + 6 week_ending, SUM(sales) FROM table GROUP BY TRUNC(whatever_time,'DAY')and you'll get what you need.
Notice that TRUNC(whatever_time,'DAY') honors the Oracle session initialization parameter called “NLS_TERRITORY”. For example, in Europe Monday is considered the first business day of the week. Try this.
ALTER SESSION SET NLS_TERRITORY=GERMANY; SELECT TRUNC( DATE '2013-12-31', 'DAY'), TRUNC( DATE '2014-01-03', 'DAY') FROM DUAL;A complete writeup of this is here: http://www.plumislandmedia.net/sql-time-processing/using-sql-report-time-intervals-oracle/
更多推荐
发布评论