-
Hi,
I am trying to write the Oracle equivalent function for isdate() of SQLServer. I have a field in Oracle as varchar2 that contains formated string dates as 'DD-MM-YYYY'. I need to chek to see if the dates are valid before pushing it into Oracle date field.
create or replace function check_dateformat(p_date_input varchar2)
return boolean is lv_date_return DATE ;
begin
lv_date_return := to_date(p_date_input, 'DD-MM-YYYY');
if
substr(p_date_input,1,2) not between '01' and '12'
or
substr(p_date_input,4,2) not between '01' and '31'
or
substr(p_date_input,7,4) not between '1900' and '2050'
or
substr(p_date_input,1,2) = '02' and substr(p_date_input,4,2) = '29'
and round(substr(p_date_input,7,4)/4)*4 <> (p_date_input,7,4)
--length(substr(p_date_input, instr(p_date_input, '-',1,2)+1))<>4
Then
return false;
else
return true;
end if;
exception
When Others then
return false;
end check_dateformat;
Can anyone help? Thanks,
CD
-
Isn't this one more simple:
Code:
create or replace function check_dateformat(p_date_input varchar2)
return boolean is
lv_date_return DATE ;
return_value BOOLEAN := TRUE;
BEGIN
BEGIN
lv_date_return := TO_DATE (p_date_input, 'DD-MM-YYYY');
IF lv_date_return NOT BETWEEN TO_DATE ('01-01-1900', 'DD-MM-YYYY') AND TO_DATE ('01-01-1900', 'DD-MM-YYYY') THEN
return_value := FALSE;
END IF;
EXCEPTION
WHEN OTHERS THEN return_value := FALSE;
END;
RETURN return_value;
END;
Why even bother with checking months and day of months, with leap years and so on - it is all implicitly checked in a TO_DATE function.
I only left the year range checking if you realy need to check this, otherwise you should simply throw that part out.
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
Ques:
Does Oracle allows you to return BOOLEAN?
thanks,
-
Actually, that has a 2-part answer
Within PL/SQL : Yes
To the outside world: No
So if you plan on calling this function from somewhere other than PL/SQL, then maybe use 1/0 or Y/N instead.
- Chris
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|