?
Solved

Converting a VARCHAR2 column to a DATE

Posted on 2004-11-17
5
Medium Priority
?
1,568 Views
Last Modified: 2008-03-06
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
0
Comment
Question by:gcarelse
5 Comments
 
LVL 1

Assisted Solution

by:thack111
thack111 earned 200 total points
ID: 12609067
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
 
LVL 6

Accepted Solution

by:
izblank earned 620 total points
ID: 12609086
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
 
LVL 4

Expert Comment

by:boriskalavsky
ID: 12609288
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
 
LVL 2

Expert Comment

by:ColinGordon
ID: 12609548
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
 

Author Comment

by:gcarelse
ID: 12609592
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

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Via a live example, show how to take different types of Oracle backups using RMAN.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses
Course of the Month16 days, 9 hours left to enroll

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question