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;
--■■■カレンダと比較する方法■■■
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