Re: Help Extracting Date

nosbush_at_gmail.com wrote:
> I am working with a table that has one of its columns setup to contain
> multiple pieces of data. For example, it contains
> username;entered_date;code1;code2;action_date;etc.... Everything is
> separated by semicolons. I need to get the data out and in separate
> columns which hasn't been a problem except for the 5th piece,
> action_date. I didn't set this table up nor do I have any way of
> changing how the data is entered; it is a little strange but this is
> what I have to work with. The code I'm working with to get the
> action_date is the following:
>
> DECODE(substr(mdt.mdta,instr(mdt.mdta,';',1,4)+1,(instr(mdt.mdta,';',1,5))-(instr(mdt.mdta,';',1,4))-1),
> '_', TO_DATE(null),
> TO_DATE(substr(mdt.mdta,instr(mdt.mdta,';',1,4)+1,(instr(mdt.mdta,';',1,5))-(instr(mdt.mdta,';',1,4))-1),
> 'ddMonyyyy')) ActionDate
>
> The problem is, I am getting error message ORA-01847: day of month must
> be between 1 and last day of month. The date format I use in the
> TO_DATE function is correct. While every row does have something in
> this column (always the username and entered_date) there isn't always
> an action_date. Sometimes it is blank or sometimes the user has
> entered '_' (not sure why but not much I can do about it). How can I
> rewrite this code so that it pulls and formats the date when it is
> present and ignores the rest?
>
> Thank you in advance for your help!
> Grant

Just to clarify the situation could you do this a paste the results back to us:

SQL> SELECT version FROM gv$instance;

SQL> set describe depth all linenum on indent on

SQL> desc <table_name>

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org