Converting a VARCHAR2 column to a DATE

Hello,

I'm using Oracle 8, and have a VARCHAR2 column.  This column consists of strings in the format 17-NOV-04 14:18.  I am doing a search based on the maximum date in this column, therefore I have to change it to a date using TO_DATE(column_name,'MM/DD/YYYY HH:MI') first.  The problem I have is that some of the entries in the column are just times (ex. '23:00') or just a single space (' ') and these won't convert to the format I specified in my TO_DATE function, so the query fails.  Is there a way to only convert the values that fit the TO_DATE format, or change the ones that don't to some default value, like '01/01/01 12:00'?


Thanks,


Grant
gcarelseAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

thack111Commented:
first thing first, the format you mentioned does not match.  Just something i noticed "17-NOV-04 14:18" 'MM/DD/YYYY HH:MI' should be 'DD-MMM-YY HH:MI'.  Not sure if you had noticed.  
The other problem you have I would try a case statment, something like:

CASE WHEN length(trim(column_name)) = 15 THEN to_date(colmn_name, 'DD-MMM-YY HH:MI')
            WHEN length(trim(column_name)) = 5 THEN to_date(colmn_name, 'HH:MI')
             ELSE  to_date('01-JAN-04 01:01', 'DD-MMM-YY HH:MI')  END as theDate

You might have to play with it a little, but the syntax should work.
if  it won't work like that then try putting the case in the to_date.

Hope that helps.
0
izblankCommented:
Create a UDF:

CREATE OR REPLACE FUNCTION my_to_date
(source_str IN VARCHAR2,
format IN VARCHAR2)
RETURN DATE
AS
BEGIN
      RETURN to_date(source_str,format);
EXCEPTION
      WHEN OTHERS THEN RETURN to_date('1/1/2001','mm/dd/yyyy');
END;

then simply
SELECT my_to_date(theColumn,theFormat)
FROM myTable
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
boriskalavskyCommented:
thack111 provided you with a solution. I only wanted to mention that you also need to take care of NULLs if you have any in this column.
0
ColinGordonCommented:
The format string should 'DD-MMM-YY HH24:MI' for a 24 hour time as in your example otherwise you will get a date format error.
0
gcarelseAuthor Commented:
Hello,

Thanks for all the help.  The UDF idea works like a charm!  Yeah, noticed that I had to add the 24 for my function to work.  Thanks again!


Regards,


Grant
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.