How to get around ORA-01722 - Invalid number

TO_DATE(your_string,'YYYYMMDDHH24MISS')

If the string comprises more data as the date component you should to extract the date substring.

You can use code similar to the following:

CREATE OR REPLACE FUNCTION convert_date(p_date_text_in IN VARCHAR2) RETURN DATE IS
BEGIN
      IF (LENGTH(p_date_text_in) = 14) THEN
       RETURN TO_DATE(TO_CHAR(datenumber), 'YYYYMMDDHH24MISS');
      ELSE IF (LENGTH(p_date_text_in) = 8) THEN
       RETURN TO_DATE(TO_CHAR(datenumber), 'YYYYMMDD');
      END IF;
      
      RETURN NULL;
END number2date;

@atlvandyguy:

Grim_toaster might give you a right solution, but a more systematic way of solving this problem should be like this:

---->your comment:  I am using the function NUMBER2DATE that I am attaching below but apparently there are some "invalid numbers.
function number2date(datenumber number) return date is



Why do you use a NUMBER as a parameter for the function ? Did not you say in the beginning : the dates are given in the string format.  " I am TRYING to convert a vendor file where their dates are given in a string which SHOULD be in the format of YYYYMMDDHH24MISS.

So Your function definition is wrong to begin with. because If your vendor file contains a date string like 'February......' your function will fail right away with ORA-01722 .





----->your comments: How could I possibly find the invalid numbers to potentially fix them before we convert?  Or, can I have an exception in an Oracle function which will catch the malformed date strings and try to convert them differently potentially.



I think if you don't want to eyeball through each line of the vendor file to make sure you code every date format into your function, you sure can try the trial-and-error approach using EXCEPTION handler.

try the following function, which will insert into a temporary table all the date string that cannot be successfully converted into a DATE.

create global temporary table stringDate
(date_string varchar2(200))
on commit preserve rows
/


function string2date(p_string varchar2) return date is
    truncate_length      number(2) := 14;     -- Length of datenumber to truncate.
    begin
         return to_date(substr(p_string, 1,14), 'YYYYMMDDHH24MISS');
         exception
              when others then
                   insert into stringDate values(p_string);
    end string2date;
/


then run your function, do a select on the temporary table to see the string format of those invalid forms...


hope this helps

I feel that the column that you are trying to convert has some characters inbetween.  Check the data.

try this,

function number2date(datenumber varchar2) return date is
     truncate_length      number(2) := 14;     -- Length of datenumber to truncate.
     timestamp_format     varchar2(20) := 'YYYYMMDDHH24MISSFF8'; -- Format the timestamp will output in.
     returndate           date;
begin
     returndate := to_date(substr(datenumber),1,14), 'YYYYMMDDHH24MISS');
     return returndate;
exception
when others then
    return null;
end number2date;