Does oracle have an ISDATE function?

[cmgaviao]

MS SQL server includes an

ISDATE() function which evaluates a string and returns a 0 or 1 indicating whether or not the string is a valid date. Is there an equivalent function in Oracle? I've looked around, and can't seem to find anything.

TIA

 

[stefanhei]

Hi.
The to_date function returns an error, if you try to convert a string, that is not a valid date.
You can use this behavior to construct a function like:
create or replace function isdate(p_inDate date, p_format varchar2) return number
as
v_dummy date;
begin
select to_date(p_inDate,p_format) into v_dummy from dual;
return 0;
exception
when others then return 1;
end isdate;


Stefan

 

[cmgaviao]

Thanks for the help Stefan.

This looks like it would do the trick just fine (I don't know that much about Oracle)...

Now, hopefully I can talk my clients into giving my app create function privileges...

If I can't, is there some way to integrate something like this into a case statement?

 

[stefanhei]

You create a stored function with the above code - so no need to create it each time it's used. The function could be created when you create the tables you need for your app.
I can't think of any way to integrate a functionality like this into plain SQL - exceptions are a PL/SQL-construct.

Stefan

 

[BJCooperIT]

In the function you do not have to have the overhead of selecting from the database. Try replacing:

select to_date(p_inDate,p_format) into v_dummy from dual;

with

v_dummy  := to_date(p_inDate,p_format);

select * from Life where Brain is not null

Consultant/Custom Forms & PL/SQL - Oracle 8.1.7 - Windows 2000
^{When posting code, please use TGML to help readability. Thanks!}