If I have a date format DDMM in PL/SQL and I want to validate it. What is the correct way to do it?
DD is the day and MM is the moth.
For an example:
0208 - is a valid date
3209 - is not a valid date
0113 - is not a valid date.
You could write a function like this one, for instance:
create or replace function is_valid(p_val in varchar2)
return number
is
not_a_valid_day exception;
not_a_valid_month exception;
pragma exception_init(not_a_valid_day, -1847);
pragma exception_init(not_a_valid_month, -1843);
l_date date;
begin
l_date := to_date(p_val, 'ddmm');
return 1;
exception
when not_a_valid_day or not_a_valid_month
then return 0;
end;
SQL> with test_dates(dt) as(
2 select '0208' from dual union all
3 select '3209' from dual union all
4 select '0113' from dual
5 )
6 select dt, is_valid(dt) as valid
7 from test_dates
8 /
DT VALID
---- ----------
0208 1
3209 0
0113 0