Oracle SQL 任何一年的第一天和最后一天
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18551333/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Oracle SQL first and last day of quarter of any year
提问by Manvi Kapoor
Is there any way i can calculate the first and last day of the three quarters in any year . 2012 , 2013 or 2014
有什么方法可以计算任何一年中三个季度的第一天和最后一天。2012 年、2013 年或 2014 年
SELECT ADD_MONTHS(TRUNC(SYSDATE, 'Q'), -3) AS First,
TRUNC(SYSDATE, 'Q') - 1 AS Last
FROM DUAL
calculates the first quarter of current year. i want to calculate the first quarter of any year ?
计算本年度的第一季度。我想计算任何一年的第一季度？
回答by Nick Krasnov
You could do the following:
您可以执行以下操作：
with q(qtr) as(
select add_months(
DATE '2013-01-01'
, (level-1)*3
)
from dual
connect by level <= 4
)
select qtr as first_day
, last_day(add_months(qtr, 2)) as last_day
from q
Result:
结果：
FIRST_DAY LAST_DAY
----------- -----------
01.01.2013 31.03.2013
01.04.2013 30.06.2013
01.07.2013 30.09.2013
01.10.2013 31.12.2013
回答by Sarathi Kamaraj
This is one way of doing it
这是一种方法
select to_date('01-JAN-'||to_char(yr), 'DD-MON-YYYY') first_qtr,
to_date('01-APR-'||to_char(yr), 'DD-MON-YYYY') second_qtr,
to_date('01-JUL-'||to_char(yr), 'DD-MON-YYYY') third_qtr,
to_date('01-OCT-'||to_char(yr), 'DD-MON-YYYY') fourth_qtr
from ( select :year yr from dual )
UNION ALL
select to_date('01-APR-'||to_char(yr), 'DD-MON-YYYY')-1 first_qtr,
to_date('01-JUL-'||to_char(yr), 'DD-MON-YYYY')-1 second_qtr,
to_date('01-OCT-'||to_char(yr), 'DD-MON-YYYY')-1 third_qtr,
to_date('01-JAN-'||to_char(yr+1), 'DD-MON-YYYY')-1 fourth_qtr
from ( select :year yr from dual )
I have used bind variables so change it to your requirements accordingly.
我已经使用了绑定变量，因此相应地将其更改为您的要求。
I am fairly new to Oracle, so other's can give a simplified code.
我对 Oracle 相当陌生，所以其他人可以提供简化的代码。
The output when given 2009 would be as below
给出 2009 时的输出如下
FIRST_QTR SECOND_QTR THIRD_QTR FOURTH_QTR
01/01/2009 04/01/2009 07/01/2009 10/01/2009
03/31/2009 06/30/2009 09/30/2009 12/31/2009
回答by David Faber
This is an old question but maybe this will be helpful:
这是一个老问题，但也许这会有所帮助：
WITH y1 AS (
SELECT LEVEL + 2000 AS the_year
FROM dual
CONNECT BY LEVEL <= 20
), q1 AS (
SELECT LEVEL AS the_quarter
FROM dual
CONNECT BY LEVEL <= 4
)
SELECT the_year, the_quarter
, TO_CHAR(first_day, 'DAY') AS first_dw, first_day
, TO_CHAR(last_day, 'DAY') AS last_dw, last_day
FROM (
SELECT the_year, the_quarter
, ADD_MONTHS(TO_DATE(the_year, 'YYYY'), 3 * (the_quarter - 1)) AS first_day
, ADD_MONTHS(TO_DATE(the_year, 'YYYY'), 3 * the_quarter) - 1 AS last_day
FROM y1, q1
)
回答by René Nyffenegger
One line per year, each line consisting of the year plus 8 (=2 dates per quarter) dates:
每年一行，每行由年份加上 8 个（=每季度 2 个日期）日期组成：
with params as (
select
2012 as start_year,
2014 as end_year
from
dual
)
select
start_year+ level - 1 year,
to_date((start_year+ level - 1) || '0101', 'yyyymmdd') start_q1,
to_date((start_year+ level - 1) || '0331', 'yyyymmdd') end_q1 ,
to_date((start_year+ level - 1) || '0401', 'yyyymmdd') start_q2,
to_date((start_year+ level - 1) || '0630', 'yyyymmdd') end_q2 ,
to_date((start_year+ level - 1) || '0701', 'yyyymmdd') start_q3,
to_date((start_year+ level - 1) || '0930', 'yyyymmdd') end_q3 ,
to_date((start_year+ level - 1) || '1001', 'yyyymmdd') start_q4,
to_date((start_year+ level - 1) || '1231', 'yyyymmdd') end_q4
from
dual, params
connect by
start_year + level -1 <= end_year;
