使用星期六作为周结束日期计算oracle中的周结束日期(Calculate the week ending date in oracle using Saturday as the week end date)

给定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/

更多推荐