ORA-01841 troubles

[alexhu]

Oracle 8.0.5.1
AIX 4.3.3

I have a table column defined as char(50)

It contains a time and date stamp defined as YY,MM,DD,HH,MI,SS

I am trying to convert this to a real date using
select to_date(PH00_CUSTOM_RESERVED, 'YY,MM,DD,HH,MI,SS') from phpick00

but get

select to_date(PH00_CUSTOM_RESERVED, 'YY,MM,DD,HH,MI,SS') from phpick00
*
ERROR at line 1:
ORA-01841: (full) year must be between -4713 and +9999, and not be 0


Any Ideas? changing the format is not an option (3rd party apps, lots of work, lots of money etc, etc)

Alex

 

[alexhu]

SantaMufasa

here are some rows that are selected as bogus by your query - I can't see anything wrong can you ?

ROWID PH00_CUSTOM_RESERVED
------------------ --------------------------------------------------
AAAWmZAANAAAanGAAC 03,11,26,07,58,12
AAAWmZAANAAAanHAAA 03,11,26,07,58,12
AAAWmZAANAAAanHAAB 03,11,26,07,58,12
AAAWmZAANAAAanHAAC 03,11,26,08,03,29
AAAWmZAANAAAanIAAA 03,11,25,02,45,29
AAAWmZAANAAAanIAAB 03,11,25,03,59,27
AAAWmZAANAAAanIAAC 03,11,25,02,44,41
AAAWmZAANAAAanJAAA 03,11,25,03,59,27
AAAWmZAANAAAanJAAB 03,11,26,08,03,29
AAAWmZAANAAAanLAAC 03,11,26,02,44,38
AAAWmZAANAAAanNAAB 03,11,26,12,52,12

Alex

 

[SantaMufasa]

Alex, my buddy,

Notice the difference between your code and mine: Mine uses "trim(...)"; yours uses "ltrim(...)". That difference makes all the difference! Since "trim(...)" was not available until 8i, you need to adjust your code to use ltrim(rtrim(...)) [and in your case, rtrim(...) only would be satisfactory because the right side is what you need to trim].

So, your code adjustment that exactly simulates my code is:

select count(*) from phpick00
where (length(trim(translate(PH00_CUSTOM_RESERVED,'^,','^')))<>12 or
length(rtrim(ltrim(translate(PH00_CUSTOM_RESERVED,'^0123456789','^'))))<>5)
and length(trim(PH00_CUSTOM_RESERVED)) is not null;

  COUNT(*)
----------
         0

Sorry I didn't test my original code on an 8.0.5.1 instance. Please let me know as soon as possible your results since we are leaving in a while.

"Thankfully",

Mufasa
(aka Dave of Sandy, Utah, USA @ 16:37 (27Nov03) GMT, 09:37 (27Nov03) Mountain Time)