Re: Help with date conversion - how to handle invalid date

Here is a safe string to date conversion (stand-alone stored function) you can use in PL/SQL as well as SQL. It returns NULL on an error. Note that if you attempt to do additional error handling, such as logging to a database table in the exception handler, you will not be able to use it in an SQL.

• Safe varchar2 to date conversion.
• This function performs the Oracle TO_DATE conversion routine for char or varchar2
• strings. On a conversion error (i.e., invalid date, invalid format) it returns NULL. CREATE OR REPLACE FUNCTION char_to_date(Pstr IN VARCHAR2, Pformat IN VARCHAR2 DEFAULT NULL) RETURN DATE IS status NUMERIC; local_status NUMERIC; BEGIN IF Pformat IS NULL THEN -- optional format not supplied RETURN (TO_DATE(Pstr)); ELSE RETURN (TO_DATE(Pstr, Pformat)); -- format supplied END IF; -- test for optional format EXCEPTION WHEN OTHERS THEN RETURN (NULL); END char_to_date; /

--
- Dan Clamage
http://www.telerama.com/~dclamage
If you haven't crashed the Server,
you haven't been trying hard enough.
Neville Sweet wrote in message
<01be231f$6f342800$483c1286_at_itwol-pc3963.itwol.bhp.com.au>...


>Hi,

>

>Add an exception to pick up the problem, eg.


>EXCEPTION

>  WHEN OTHERS

>-- whatever ---

>

>This will pick up any error in the entire procedure, so you could isolate

>date problems by writing a function that only does the date conversion, eg.

>ELSE

>  var_check_date := check_date_valid(tr_rec.inventory_end_date);

>  IF var_check_date IS NULL THEN

>-- whatever ---

>END;

>

>FUNCTION check_date_valid (tx_date_in IN VARCHAR2)

>          RETURN DATE IS

>  dt_return DATE;

>BEGIN

>  dt_return := TO_DATE(tx_date_in, 'YYYYMMDDW');

>     -- in your example, the quotes were missing. --

>  RETURN dt_return;

>

>EXCEPTION

>  WHEN OTHERS

>    null;

>-- or message or whatever --

>END;

>

>ellen_n_paul_at_my-dejanews.com wrote in article

><74k6ld$imf$1_at_nnrp1.dejanews.com>...

>> I'm fairly new at PL/SQL and cannot figure out how to deal with a date

>> conversion problem:

>>

>> I am receiving a date in a 13 char field.  I would like to place it into

>an

>> Oracle date field.  Is there anyway for me to check it's validity prior

>to

>> placing it into the date field and causing an exception if it is not a

>valid

>> date.  If not, is it possible to me to handle the exception within the

>current

>> block of code?

>>

>> here is a sample of the problem:

>>

>> IF tr_rec.inventory_end_date is not null THEN

>>    err_msg:= 'INVENTORY END DATE '||blank_mand_msg;

>>    INSERT INTO netout_msgs (net_input_data) VALUES (err_msg);

>>    rec_err_count := rec_err_count + 1;

>> /* ELSE

>>      var_check_date := TO_DATE(tr_rec.inventory_end_date, YYYYMMDDW); */

>> END IF;

>>

>> I have searched your archives for a similar problem but could not locate

>one,

>> if I am repeating something that has been answered before, I apologize.

>Any

>> help or referral to help would be appreciated.  Thank you.

>>

>> Ellen V.

>>

>> -----------== Posted via Deja News, The Discussion Network ==----------

>> http://www.dejanews.com/       Search, Read, Discuss, or Start Your Own

>

>>