NEXT_DAY関数、TRUNC関数を使って、向こう1000日分のプレミアムフライデーを求めてみる。(Oracle Database)
これでワイもプレミアムや!彡(゚)(゚)
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY/MM/DD'; SELECT TRUNC(NEXT_DAY(SYSDATE+LEVEL, 'friday'), 'MM') AS TUKI , MAX(TRUNC(NEXT_DAY(SYSDATE+LEVEL, 'friday'), 'DD')) AS PREMIUM FROM DUAL CONNECT BY LEVEL <= 1000 GROUP BY TRUNC(NEXT_DAY(SYSDATE+LEVEL, 'friday'), 'MM') ORDER BY TUKI; TUKI PREMIUM ---------- ---------- 2017/03/01 2017/03/31 2017/04/01 2017/04/28 2017/05/01 2017/05/26 2017/06/01 2017/06/30 2017/07/01 2017/07/28 2017/08/01 2017/08/25 2017/09/01 2017/09/29 2017/10/01 2017/10/27 2017/11/01 2017/11/24 2017/12/01 2017/12/29 2018/01/01 2018/01/26 TUKI PREMIUM ---------- ---------- 2018/02/01 2018/02/23 2018/03/01 2018/03/30 2018/04/01 2018/04/27 2018/05/01 2018/05/25 2018/06/01 2018/06/29 2018/07/01 2018/07/27 2018/08/01 2018/08/31 2018/09/01 2018/09/28 2018/10/01 2018/10/26 2018/11/01 2018/11/30 2018/12/01 2018/12/28 TUKI PREMIUM ---------- ---------- 2019/01/01 2019/01/25 2019/02/01 2019/02/22 2019/03/01 2019/03/29 2019/04/01 2019/04/26 2019/05/01 2019/05/31 2019/06/01 2019/06/28 2019/07/01 2019/07/26 2019/08/01 2019/08/30 2019/09/01 2019/09/27 2019/10/01 2019/10/25 2019/11/01 2019/11/29 33 rows selected.
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY/MM/DD'; WITH rec(lv) AS ( SELECT 1 FROM DUAL UNION ALL SELECT lv + 1 FROM REC WHERE lv < 1000 ) SELECT TRUNC(NEXT_DAY(SYSDATE+lv, 'friday'), 'MM') AS TUKI , MAX(TRUNC(NEXT_DAY(SYSDATE+lv, 'friday'), 'DD')) AS PREMIUM FROM rec GROUP BY TRUNC(NEXT_DAY(SYSDATE+lv, 'friday'), 'MM') ORDER BY TUKI; TUKI PREMIUM ---------- ---------- 2017/03/01 2017/03/31 2017/04/01 2017/04/28 2017/05/01 2017/05/26 2017/06/01 2017/06/30 2017/07/01 2017/07/28 2017/08/01 2017/08/25 2017/09/01 2017/09/29 2017/10/01 2017/10/27 2017/11/01 2017/11/24 2017/12/01 2017/12/29 2018/01/01 2018/01/26 TUKI PREMIUM ---------- ---------- 2018/02/01 2018/02/23 2018/03/01 2018/03/30 2018/04/01 2018/04/27 2018/05/01 2018/05/25 2018/06/01 2018/06/29 2018/07/01 2018/07/27 2018/08/01 2018/08/31 2018/09/01 2018/09/28 2018/10/01 2018/10/26 2018/11/01 2018/11/30 2018/12/01 2018/12/28 TUKI PREMIUM ---------- ---------- 2019/01/01 2019/01/25 2019/02/01 2019/02/22 2019/03/01 2019/03/29 2019/04/01 2019/04/26 2019/05/01 2019/05/31 2019/06/01 2019/06/28 2019/07/01 2019/07/26 2019/08/01 2019/08/30 2019/09/01 2019/09/27 2019/10/01 2019/10/25 2019/11/01 2019/11/29 33 rows selected.
Qiitaにも書いたやで彡(^)(^)
NEXT_DAY関数、TRUNC関数を使って、向こう1000日分のプレミアムフライデーを求めてみる。
(Oracle Database)
http://qiita.com/ora_gonsuke777/items/a4e7c1d5e4c5c7a462c3