トップページに戻る    次のSQLパズルへ    前のSQLパズルへ

10-41 varchar2型の日付チェック

SQLパズル

date_testテーブル
date_value
==========
2002-12-31
2003-01-31
2003-02-28
2003-02-29
2003-02-30
2003-03-31
2003-04-30
2003-04-3010
2004-02-28
2004-02-29
----------
2003304430
2004-04-31
2004-13-10
abcdefg
null
平成17年11月

date_testテーブルの、年度が2002年か2003年のレコードの、
date_valueと、年度を出力する。

ただし、
date_valueが、yyyy-mm-ddの日付書式でdate型にキャストできない場合は、出力しない。

出力結果
date_value  年度
----------  ----
2002-12-31  2002
2003-01-31  2002
2003-02-28  2002
2003-03-31  2002
2003-04-30  2003
2004-02-28  2003
2004-02-29  2003

こちらを参考にさせていただきました


データ作成スクリプト

create table date_test(date_value varchar2(16));

insert into date_test values('2002-12-31');
insert into date_test values('2003-01-31');
insert into date_test values('2003-02-28');
insert into date_test values('2003-02-29');
insert into date_test values('2003-02-30');
insert into date_test values('2003-03-31');
insert into date_test values('2003-04-30');
insert into date_test values('2003-04-3010');
insert into date_test values('2004-02-28');
insert into date_test values('2004-02-29');
insert into date_test values('----------');
insert into date_test values('2003304430');
insert into date_test values('2004-04-31');
insert into date_test values('2004-13-10');
insert into date_test values('abcdefg');
insert into date_test values(null);
insert into date_test values('平成17年11月');
commit;


SQL

--■■■カレンダと比較する方法■■■
select date_value,
to_char(add_months(to_date(date_value,'yyyy-mm-dd'),-3),'yyyy') as 年度
from date_test a
where exists(select a.date_value from dual
             intersect
             select to_char(to_date('20020401','yyyymmdd')+RowNum-1,'yyyy-mm-dd')
               from (select 1 from all_catalog where RowNum <= 366*2)
                      where to_date('20020401','yyyymmdd')+RowNum-1
                         <= to_date('20040331','yyyymmdd'))
order by date_value;

--■■■短絡orで文字列をチェックする方法■■■
select date_value,年度
from(
select date_value,
to_char(add_months(to_date(
case
when date_value is null then null
when Length(date_value) != Length('yyyy-mm-dd') then null
when translate(date_value,'a-0123456789','a') is not null then null
when Length(Replace(date_value,'-')) != 8 then null
when Replace(date_value,'-') is null then null
when substr(date_value,5,1) != '-' or substr(date_value,8,1) != '-' then null
when to_number(substr(date_value,6,2)) not between 1 and 12 then null
when to_number(substr(date_value,9,2)) >
     30+ case when to_number(substr(date_value,6,2)) in(4,6,9,11) then 0 else 1 end
     then null
when to_number(substr(date_value,6,2)) = 2
 and to_number(substr(date_value,9,2)) >
     28+ decode(to_char(to_date(substr(date_value,1,4) || '1231','yyyymmdd'),'ddd'),'366',1,0)
     then null
else date_value end,'yyyy-mm-dd'),-3),'yyyy') as 年度
from date_test)
where 年度 between '2002' and '2003'
order by date_value;

--■■■上のクエリで正規表現を使用(10g以降)■■■
select date_value,年度
from(
select date_value,
to_char(add_months(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,6,2)) not between 1 and 12 then null
when to_number(substr(date_value,9,2)) >
     30+ case when to_number(substr(date_value,6,2)) in(4,6,9,11) then 0 else 1 end
     then null
when to_number(substr(date_value,6,2)) = 2
 and to_number(substr(date_value,9,2)) >
     28+ decode(to_char(to_date(substr(date_value,1,4) || '1231','yyyymmdd'),'ddd'),'366',1,0)
     then null
else date_value end,'yyyy-mm-dd'),-3),'yyyy') as 年度
from date_test)
where 年度 between '2002' and '2003'
order by date_value;


解説

検索case式は、
when句がTrueになったら、後続のwhen句を評価しないので、
短絡ORを実現できます。

単純にVBのIsDate関数のような関数を
Oracleで作成したい場合は、
以下のようなストアドファンクションを作成すると簡単でしょう。

create or replace function IsDate(HikiStr IN varchar2) return number is
    work date;
begin
    if HikiStr is null then
        return 0;
    end if;
    work := to_date(HikiStr,'yyyy-mm-dd');
    return 1;
exception
when others then
    return 0;
end;
/

select date_value,IsDate(date_value) as IsDateResult
  from date_test
order by date_value;

date_value  ■IsDateResult
============■============
----------  ■           0
2002-12-31  ■           1
2003-01-31  ■           1
2003-02-28  ■           1
2003-02-29  ■           0
2003-02-30  ■           0
2003-03-31  ■           1
2003-04-30  ■           1
2003-04-3010■           0
2003304430  ■           0
2004-02-28  ■           1
2004-02-29  ■           1
2004-04-31  ■           0
2004-13-10  ■           0
abcdefg     ■           0
平成17年11月■           0
ヌル        ■           0