Re: Oracle equivalent of IsDate expression

KurtisK wrote:
> We had the same problem when porting from SQLServer to Oracle. Oracle was
> missing several key functions that you'd think they'd have by now.
>
> I would suggest creating a wrapper function for TO_DATE. Let the Exception
> handler return null if the conversion fails. Be aware though that the
> TO_DATE function depends on the NLS_DATE_FORMAT value if you do not
> explicitly specifiy
> it. ie. 041310 would be a compatible 'dmy' format ( day before month ) , but
> not 'mdy'. ( month before day)
>
> In your case, it sounds like you know the format of this date string in
> advance, so you can be explicit with 'YYMMDD'.
>
>
> FUNCTION TO_DATE2(vcDate IN VARCHAR2)
> RETURN DATE
> IS
>
> dtDate DATE;
> BEGIN
>
>
> -- you could do substring here on vcDate to hide it from the view code
>
> RETURN( TO_DATE(vcDate, 'YYMMDD' ));
>
> EXCEPTION
>
> WHEN OTHERS
>
> THEN RETURN NULL;
>
>
> END;
>
>
>
>
>
>
>
>
>

Thanks all

The following does the trick nicely (and includes a format string so it can be used elsewhere):

CREATE OR REPLACE FUNCTION AS_DATE(vcDate IN VARCHAR2, vcFormat IN VARCHAR2) RETURN DATE IS         

dtDate DATE;         

BEGIN
                         dtDate := TO_DATE(vcDate, vcFormat);

                         RETURN dtDate;         

			 EXCEPTION WHEN OTHERS THEN			 								
			 RETURN NULL;	

END AS_DATE;/ Paul BJ Received on Wed Oct 27 2004 - 16:44:11 CDT