Re: Help Extracting Date

On Jan 3, 4:40 pm, "sybrandb" <sybra..._at_gmail.com> wrote:
> nosb..._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!
> > GrantYour question demonstrates there is no input validation from the piece
> of software you call 'application'. As there is no is_date function (or
> you would need to write one), and it is impossible to cater for all
> exceptions, judging the code above is fully unreadable due to hardcoded
> literals, I would strongly recommend dumping this crap.
> You should never ever concatenate multiple columns into one, as it is
> always easier to get concatenated output, as opposed to 'parse' the
> string provided. That would only work if you wrote a true BNF
> compatible parser for this string. But you are working with a RDBMS,
> and rright now the 'application' is misusing your RDBMS as a flat file
> system.
> Get that trash can and dump that piece of shit.
>
> --
> Sybrand Bakker
> Senior Oracle DBA

I agree with you completely. This wasn't setup wisely. However, I'm in the middle and have to work with what I have. Is there any way to put error checking in the code so that when an error is produced, a null date value is returned? Received on Wed Jan 03 2007 - 16:46:25 CST