oracle sql 最近5个季度的最后一天
答案:2 悬赏:40
解决时间 2021-01-04 00:21
- 提问者网友:写不出迷人情诗
- 2021-01-03 07:18
求大神的s帮忙,最近5个季度的最后一天,如果当前日期为季度末的一天,以当前日期为第一个日期,如果当前日期部位季度末的一天,以上个季度末的最后一天第一个日期,
最佳答案
- 二级知识专家网友:野心和家
- 2021-01-03 08:07
ALTER session SET nls_date_format='yyyy-mm-dd';
with mycte AS (
SELECt TRUNC( SYSDATE + 1, 'Q' ) AS TopDay FROM DUAL
)
SELECt TopDay -1 AS Result FROM mycte UNIOn ALL
SELECt ADD_MONTHS(TopDay,3) -1 AS Result FROM mycte UNIOn ALL
SELECt ADD_MONTHS(TopDay,6) -1 AS Result FROM mycte UNIOn ALL
SELECt ADD_MONTHS(TopDay,9) -1 AS Result FROM mycte UNIOn ALL
SELECt ADD_MONTHS(TopDay,12) -1 AS Result FROM mycte;
RESULT
----------
2013-03-31
2013-06-30
2013-09-30
2013-12-31
2014-03-31
-- 假如今天是 季度的最后一天.(例如 2013-06-30 )
with mycte AS (
SELECt TRUNC( TO_DATE('20130630', 'YYYYMMDD') + 1, 'Q' ) AS TopDay FROM DUAL
)
SELECt TopDay -1 AS Result FROM mycte UNIOn ALL
SELECt ADD_MONTHS(TopDay,3) -1 AS Result FROM mycte UNIOn ALL
SELECt ADD_MONTHS(TopDay,6) -1 AS Result FROM mycte UNIOn ALL
SELECt ADD_MONTHS(TopDay,9) -1 AS Result FROM mycte UNIOn ALL
SELECt ADD_MONTHS(TopDay,12) -1 AS Result FROM mycte;
RESULT
----------
2013-06-30
2013-09-30
2013-12-31
2014-03-31
2014-06-30
with mycte AS (
SELECt TRUNC( SYSDATE + 1, 'Q' ) AS TopDay FROM DUAL
)
SELECt TopDay -1 AS Result FROM mycte UNIOn ALL
SELECt ADD_MONTHS(TopDay,3) -1 AS Result FROM mycte UNIOn ALL
SELECt ADD_MONTHS(TopDay,6) -1 AS Result FROM mycte UNIOn ALL
SELECt ADD_MONTHS(TopDay,9) -1 AS Result FROM mycte UNIOn ALL
SELECt ADD_MONTHS(TopDay,12) -1 AS Result FROM mycte;
RESULT
----------
2013-03-31
2013-06-30
2013-09-30
2013-12-31
2014-03-31
-- 假如今天是 季度的最后一天.(例如 2013-06-30 )
with mycte AS (
SELECt TRUNC( TO_DATE('20130630', 'YYYYMMDD') + 1, 'Q' ) AS TopDay FROM DUAL
)
SELECt TopDay -1 AS Result FROM mycte UNIOn ALL
SELECt ADD_MONTHS(TopDay,3) -1 AS Result FROM mycte UNIOn ALL
SELECt ADD_MONTHS(TopDay,6) -1 AS Result FROM mycte UNIOn ALL
SELECt ADD_MONTHS(TopDay,9) -1 AS Result FROM mycte UNIOn ALL
SELECt ADD_MONTHS(TopDay,12) -1 AS Result FROM mycte;
RESULT
----------
2013-06-30
2013-09-30
2013-12-31
2014-03-31
2014-06-30
全部回答
- 1楼网友:苦柚恕我颓废
- 2021-01-03 08:23
提问要写清楚问题,最好举例说明。
再看看别人怎么说的。
我要举报
如以上问答内容为低俗、色情、不良、暴力、侵权、涉及违法等信息,可以点下面链接进行举报!
大家都在看
推荐资讯
• 手机登qq时,显示手机磁盘不足,清理后重新登 |
• 刺客的套装怎么选啊? |