Возможно, для кого-то это будет открытием, но Oracle Database допускает вставку таких дат, как 127-ое мая или 51.51.2009. :) Данные, которые передаются во внутреннем формате, например, через OCI или EXP/IMP, не проверяются. Но потом при использовании этих данных в приложении возникают ошибки вроде ORA-01801. И эти данные нужно найти и исправить.
было найдено несколько следующих решений для задачи поиска.
Итак, данные:
CREATE TABLE tmp(ID NUMBER PRIMARY KEY, d DATE);
declare
d date;
BEGIN
INSERT INTO tmp VALUES(1, to_date('01.12.1900', 'dd.mm.yyyy'));
INSERT INTO tmp VALUES(2, to_date('01.12.-1900', 'dd.mm.syyyy'));
dbms_stats.convert_raw_value(hextoraw('7764057f7f77aa'), d);
INSERT INTO tmp VALUES(3, d);
dbms_stats.convert_raw_value(hextoraw('F7640d01010101'), d);
INSERT INTO tmp VALUES(4, d);
COMMIT;
end;/
Следующие решения основываются на формате хранения даты в Oracle Database. Если рассмотреть побайтно дамп даты, то видны следующие компоненты:
Номер байта слева - Компонент
в функции DUMP
1 - столетие + 100
2 - год в столетии + 100
3 - месяц
4 - день
5 - час + 1
6 - минута + 1
7 - секунда + 1
Решение "в лоб", через указания границ для компонентов дампа даты:
WITH t AS
(SELECT id, DUMP(d) AS dmp
FROM tmp),
comps as
(SELECT id, dmp
, substr(dmp, instr(dmp, ':', 1, 1)+1, instr(dmp, ',', 1, 1) - instr(dmp, ':') - 1) - 100 cc
, substr(dmp, instr(dmp, ',', 1, 1)+1, instr(dmp, ',', 1, 2) - instr(dmp, ',', 1, 1) - 1) - 100 yy
, substr(dmp, instr(dmp, ',', 1, 2)+1, instr(dmp, ',', 1, 3) - instr(dmp, ',', 1, 2) - 1) mm
, substr(dmp, instr(dmp, ',', 1, 3)+1, instr(dmp, ',', 1, 4) - instr(dmp, ',', 1, 3) - 1) dd
, substr(dmp, instr(dmp, ',', 1, 4)+1, instr(dmp, ',', 1, 5) - instr(dmp, ',', 1, 4) - 1) - 1 hh
, substr(dmp, instr(dmp, ',', 1, 5)+1, instr(dmp, ',', 1, 6) - instr(dmp, ',', 1, 5) - 1) - 1 mi
, substr(dmp, instr(dmp, ',', 1, 6)+1) - 1 ss
from t)
select id, dmp from comps
where cc not between -47 and 99
or (yy not between -99 and 99 or (cc = -47 and yy < -12))
or mm not between 1 and 12
or (mm in (1, 3, 5, 7, 8, 10, 12) and dd not between 1 and 31)
or (mm in (4, 6, 9, 11) and dd not between 1 and 30)
or ((mod(cc, 4) = 0 or (mod(cc, 4) <> 0 and mod(yy, 4) = 0)) and mm = 2 and dd not between 1 and 29)
or (mod(cc, 4) <> 0 and mod(yy, 4) <> 0 and mm = 2 and dd not between 1 and 28)
or hh not between 0 and 23
or mi not between 0 and 59
or ss not between 0 and 59;
Решение, использующее функцию IsDateValid, автор функции Elic:
create or replace function IsDateValid(aDate varchar2, aFormat varchar2) return int
is
d date;
begin
d := to_date(aDate, aFormat);
return 1;
exception
when others then
if sqlcode between -1899 and -1800 then
return 0;
else
raise;
end if;
end IsDateValid;
/
WITH t AS
(SELECT id, DUMP(d) AS dmp
FROM tmp),
comps as
(SELECT id, dmp
, substr(dmp, instr(dmp, ':', 1, 1)+1, instr(dmp, ',', 1, 1) - instr(dmp, ':') - 1) - 100 cc
, substr(dmp, instr(dmp, ',', 1, 1)+1, instr(dmp, ',', 1, 2) - instr(dmp, ',', 1, 1) - 1) - 100 yy
, substr(dmp, instr(dmp, ',', 1, 2)+1, instr(dmp, ',', 1, 3) - instr(dmp, ',', 1, 2) - 1) mm
, substr(dmp, instr(dmp, ',', 1, 3)+1, instr(dmp, ',', 1, 4) - instr(dmp, ',', 1, 3) - 1) dd
, substr(dmp, instr(dmp, ',', 1, 4)+1, instr(dmp, ',', 1, 5) - instr(dmp, ',', 1, 4) - 1) - 1 hh
, substr(dmp, instr(dmp, ',', 1, 5)+1, instr(dmp, ',', 1, 6) - instr(dmp, ',', 1, 5) - 1) - 1 mi
, substr(dmp, instr(dmp, ',', 1, 6)+1) - 1 ss
from t)
select id, dmp
from comps
where IsDateValid( to_char(dd, '900.')||to_char(mm, '900.')||to_char(cc*100+yy, '90000')||to_char(hh, '900')||':'||to_char(mi, '900')||':'||to_char(ss, '900')
, 'DD.MM.SYYYY HH24:MI:SS') = 0;
Решение, использующее собственную функцию IsDateDumpValid:
create or replace function IsDateDumpValid(p_date date)
return integer
is
l_date_str varchar2(32);
l_try_date date;
begin
select to_char(dd, '900.')||to_char(mm, '900.')||to_char(cc*100+yy, '90000')||to_char(hh, '900')||':'||to_char(mi, '900')||':'||to_char(ss, '900')
into l_date_str
from (SELECT substr(dmp, instr(dmp, ':', 1, 1)+1, instr(dmp, ',', 1, 1) - instr(dmp, ':') - 1) - 100 cc
, substr(dmp, instr(dmp, ',', 1, 1)+1, instr(dmp, ',', 1, 2) - instr(dmp, ',', 1, 1) - 1) - 100 yy
, substr(dmp, instr(dmp, ',', 1, 2)+1, instr(dmp, ',', 1, 3) - instr(dmp, ',', 1, 2) - 1) mm
, substr(dmp, instr(dmp, ',', 1, 3)+1, instr(dmp, ',', 1, 4) - instr(dmp, ',', 1, 3) - 1) dd
, substr(dmp, instr(dmp, ',', 1, 4)+1, instr(dmp, ',', 1, 5) - instr(dmp, ',', 1, 4) - 1) - 1 hh
, substr(dmp, instr(dmp, ',', 1, 5)+1, instr(dmp, ',', 1, 6) - instr(dmp, ',', 1, 5) - 1) - 1 mi
, substr(dmp, instr(dmp, ',', 1, 6)+1) - 1 ss
from (select dump(p_date) dmp from dual) t) comps;
begin
l_try_date := to_date(l_date_str, 'DD.MM.SYYYY HH24:MI:SS');
return 1;
exception
when others then
if sqlcode between -1899 and -1800 then
return 0;
else
raise;
end if;
end;
end;
/
select id from tmp where IsDateDumpValid(d) = 0;
Элегантное решение от Elic'а, использующее переполнение разрядов в дате. (К сожалению, им нельзя поймать случаи неправильных годов в датах.)
select id from tmp
where d <> d + numtodsinterval(trunc(1e8/3),'second') - numtodsinterval(trunc(1e8/3),'second');
Сообщения
