Oracleで文字列型のDate文字列をYYYY年MM月DD日形式に直す

やりたい事

• 文字列でYYYYMMDD形式の８文字がデータとして入っている
• SQLだけで、日付型として認識したい
• 日付型以外はNULLをとする
• フォーマットは YYYY年MM月DD日 にしたい

やること

SQL芸

SELECT
    TO_CHAR(TO_DATE(CASE 
        WHEN date_value IS NULL THEN NULL
        WHEN NOT RegExp_Like(date_value,'^[0-9]{4}[0-9]{2}[0-9]{2}$') THEN NULL
        WHEN TO_NUMBER(SUBSTR(date_value,5, 2)) NOT BETWEEN 1 AND 12 THEN NULL
        WHEN TO_NUMBER(SUBSTR(date_value,7, 2)) >
            30+ CASE WHEN TO_NUMBER(SUBSTR(date_value,5 ,2)) IN(4, 6, 9, 11) THEN 0 ELSE 1 END
            THEN NULL
        WHEN TO_NUMBER(SUBSTR(date_value,5, 2)) = 2
            AND TO_NUMBER(SUBSTR(date_value,7, 2)) >
            28+ DECODE(TO_CHAR(TO_DATE(SUBSTR(date_value, 1, 4) || '1231','yyyymmdd'),'ddd'),'366', 1, 0)
            THEN NULL
    ELSE date_value END, 'YYYYMMDD'), 'YYYY"年"MM"月"DD"日"') as prefix 
FROM (
    SELECT '20170331' as date_value from dual
);

結果

2017年03月31日

参考

http://d.hatena.ne.jp/cloned/20060124
http://www.geocities.jp/oraclesqlpuzzle/10-41.html