problems with different date formats in same field.

[kss444]

I am new to Oracle, and trying to work my way throught it. We have a field (file_transmission_date) this field is a VARCHAR2(20) and it contains 2 different date formats \'02/27/2007\' and 03-NOV-05. Now I have a stored proc that I wrote that needs to select everything where the file_transmission_date between blah and blah. As you can guess the proc fails because of the date formats. Do you know any way around this? I was thinking that the DECODE could help but I am not sure.

Thanks,
ks

 

[BJCooperIT]

An Oracle function will help here. You pass the file_transmission_date string to it and it returns the date.

CREATE OR REPLACE FUNCTION f_convert_string_to_date(p_text IN VARCHAR2)
    RETURN DATE 
IS
    v_date DATE := NULL;
BEGIN
    BEGIN
        v_date := to_date(p_text,'dd-mon-yy');
        RETURN(v_date);
        EXCEPTION
        WHEN OTHERS THEN NULL;
    END;
    v_date := to_date(substr(p_text,1,10),'mm/dd/yyyy');
    RETURN(v_date);

    EXCEPTION
    WHEN OTHERS THEN RETURN(v_date);
    
END f_convert_string_to_date;

To use

SELECT   f_convert_string_to_date('02/27/2007\') cnv_date
FROM dual
UNION
SELECT   f_convert_string_to_date('03-NOV-05')
FROM dual;

CNV_DATE
11/03/2005
02/27/2007

[sup]Beware of false knowledge; it is more dangerous than ignorance.[/sup][sup] ~George Bernard Shaw[/sup]
Consultant Developer/Analyst Oracle, Forms, Reports & PL/SQL (Windows)
My website: www.EmuProductsPlus.com

 

[carp]

Or even better (if allowable) would be to write a routine that standardizes your date format.
Of course, best of all would be to change the column to a DATE datatype!