如何确定Oracle查询中的季度 [英] How to determine week of a quarter in Oracle query

问题描述

我想从Oracle中的sql日期中找到四分之一周.

I want to find the Week of a Quarter from a sql date in Oracle.

我在下面的查询中尝试查找年，季度和周. 但是周"字段给出的是每月的周"，而不是季度的周"

I tried below query to find the year, quarter and week. But the week field gives the 'Week of the month' not the 'Week of the quarter'

选择to_char(sysdate，'YYYY')|| '-Q'|| to_char(sysdate，'Q')|| '-W'|| > to_char(sysdate，'w')作为当前时间" 从双重；

select to_char(sysdate, 'YYYY')|| '-Q' || to_char(sysdate, 'Q') || '-W' || >to_char(sysdate, 'w') as "Current Time" from dual;

当日期位于该月的第3周时，上述查询将返回"2016-Q2-W3".

Above query returns '2016-Q2-W3' as the date falls in the 3rd week of the month.

说日期为"2016年6月17日" 我期望结果为

Say sysdate is '17th June, 2016' I am expecting result as

2016-Q2- W12

我的周范围是(周日-周六)

My Week range is (Sunday - Saturday)

由于"2016年6月17日"属于该季度的第12周，因此应该为W12.

Since the '17th June, 2016' comes under 12th week of the quarter, it should be W12.

谢谢.

推荐答案

这将获取该季度的星期(星期日-星期六):

This will get the week (Sunday - Saturday) of the quarter:

SELECT TO_CHAR( SYSDATE, 'YYYY-"Q"Q-"W"' )
     || ( 7 + TRUNC( SYSDATE + 1, 'IW' ) - TRUNC( TRUNC( SYSDATE, 'Q' ) + 1, 'IW' ) ) / 7;
         AS "Current Time"
FROM DUAL

说明:

• 您可以使用NEXT_DAY( TRUNC( date_value ), 'SUNDAY' ) - 7(取决于NLS_TERRITORY设置)或TRUNC( date_value + 1, 'IW' ) - 1(较短且不取决于任何设置)找到在给定日期之前或前后的星期日).
• TRUNC( date_value, 'Q' )给出包含value日期的季度第一天的日期(即1月1日，4月1日，7月1日或10月1日).
• 将两者放在一起，由TRUNC( TRUNC( date_value, 'Q' ) + 1, 'IW' ) - 1给出季度的第一天或之前的星期日 因此，
• 因此，从季度开始前的星期日左右或到给定日期的星期日左右之间的天数为:( TRUNC( date_value + 1, 'IW' ) - 1 ) - ( TRUNC( TRUNC( date_value, 'Q' ) + 1, 'IW' ) - 1 )-可以通过取消-1条款.
• 周数差就是该数除以7(但给出0索引值，并且您希望将本季度的周数以1索引；您可以在结果中加上1周，或者在进行划分，请增加7天.

• You can find the Sunday which was either on-or-just-before a given date using NEXT_DAY( TRUNC( date_value ), 'SUNDAY' ) - 7 (which is dependant on the NLS_TERRITORY setting) or TRUNC( date_value + 1, 'IW' ) - 1 (which is shorter and not dependant on any settings).
• TRUNC( date_value, 'Q' ) gives the date of the first day of the quarter containing the value date (i.e. either 1st January, 1st April, 1st July or 1st October).
• Putting the two together, the Sunday on-or-just-before the first day of the quarter is given by TRUNC( TRUNC( date_value, 'Q' ) + 1, 'IW' ) - 1
• Therefore, the number of days between the Sunday on-or-just-before the start of the quarter and the Sunday on-or-just-before a given date is: ( TRUNC( date_value + 1, 'IW' ) - 1 ) - ( TRUNC( TRUNC( date_value, 'Q' ) + 1, 'IW' ) - 1 ) - which can be simplified by cancelling the -1 terms.
• The number of weeks difference is just that number divided by 7 (but gives a 0-indexed value and you want the week number of the quarter to be 1-indexed; you either add 1 week to the result or, prior to doing the division, add 7 days).

这篇关于如何确定Oracle查询中的季度的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！