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

 

[ddrillich]

Your select statement looks just fine, as you can see here:

select to_date('02,03,21,06,45,34','YY,MM,DD,HH,MI,SS') from dual, which returns: Mar 21 2002 06:45:34:000AM

So most likely PH00_CUSTOM_RESERVED is not formatted as 'YY,MM,DD,HH,MI,SS' all across.

Regards,
Dan

 

[alexhu]

Indeed, its a char(50) and looks like this

PH00_CUSTOM_RESERVED
--------------------------------------------------
03,10,31,07,59,27
03,10,31,07,59,27
03,10,31,07,59,27
03,10,31,07,48,17
03,10,31,07,59,27
03,10,31,07,59,27
03,10,28,11,09,15

So how do I pad out the format ?

Alex

 

[dbtoo2001]

Did you check the NLS settings for your database?

 

[ddrillich]

As you probably know, the fact that the column is defined as char(50) means that it's padded at the end with spaces.
I tried the following:
select to_date('___02,03,21,06,45,34________________','YY,MM,DD,HH,MI,SS') from dual where _ represent spaces and the spaces were ignored by the to_date command.

So, anybody with other ideas?

Regards,
Dan

 

[ddrillich]

Alex,

You asked how to pad the format. So, even though the spaces seem to be ignored by to_date you can just trim your PH00_CUSTOM_RESERVED column:

select to_date(TRIM(PH00_CUSTOM_RESERVED), 'YY,MM,DD,HH,MI,SS') from phpick00

Cheers,
Dan

 

[dbtoo2001]

I believe what ddrillich indicated in a previous post, is that PHOO_CUSTOM_RESERVERD probably has a null in its date somewhere as illustrated by:

08:41:47 ORIONPRD@oriongd03:SQL> /
select to_date(',03,21,06,45,34 ','YY,MM,DD,HH,MI,SS') from dual
*
ERROR at line 1:
ORA-01841: (full) year must be between -4713 and +9999, and not be 0

Check that all values in the column are valid.

 

[alexhu]

tried the trim suggestion - now I get this

SQL> select to_date(TRIM(PH00_CUSTOM_RESERVED), 'YY,MM,DD,HH,MI,SS') from phpick00;
select to_date(TRIM(PH00_CUSTOM_RESERVED), 'YY,MM,DD,HH,MI,SS') from phpick00
*
ERROR at line 1:
ORA-00904: invalid column name

Alex

 

[dbtoo2001]

I think it's RTRIM, but I also don't think it will help.
What is the count for the following?

select count(phoo_custom_reserved) from phpick00 where phoo_custom_reserved like ',%';

And is the column defined to allow nulls?

 

[alexhu]

if I use rtrim I get date but no time (and its really the time we want)

1* select count(ph00_custom_reserved) from phpick00 where ph00_custom_reserved like ',%'
SQL> /

COUNT(PH00_CUSTOM_RESERVED)
---------------------------
0

Alex

 

[dbtoo2001]

How about:
select count(phoo_custom_reserved) from phpick00 where phoo_custom_reserved like '%,,%' or phoo_custom_reserved like ', %' ;

 

[alexhu]

COUNT(PH00_CUSTOM_RESERVED)
---------------------------
0

Alex

 

[dbtoo2001]

You didn't reply to whether or not nulls are permitted:

select to_date(RTRIM(PH00_CUSTOM_RESERVED), 'YY,MM,DD,HH,MI,SS') from phpick00
where phoo_custom_reserved is not null;

 

[ddrillich]

Comparing the results of the following two queries should indicate whether all the values in the PH00_CUSTOM_RESERVED column conform to the proper format.

select count(*) from <table>

select count(*) from <table> where trim(PH00_CUSTOM_RESERVED) like '__,__,__,__,__,__'

Dan

 

[SantaMufasa]

If I may suggest a test SELECT which, if the result > 0, indicates how many rows in your data are bogus:

select count(*) from <table>
where length(trim(translate(PH00_CUSTOM_RESERVED,'^,','^')))<>12 or
length(trim(translate(PH00_CUSTOM_RESERVED,'^0123456789','^')))<>5;

&quot;12&quot; ensures you have the requisite 12 numeric digits, exactly, and &quot;5&quot; ensures that you have exactly 5 commas.

Let us know what you discover.

Mufasa
(aka Dave of Sandy, Utah, USA @ 19:27 (26Nov03) GMT, 12:27 (26Nov03) Mountain Time)

 

[SantaMufasa]

...and if you wish to see the bogus contents, then just replace the &quot;count(*)&quot; with &quot;rowid,PH00_CUSTOM_RESERVED&quot;; adding rowid allows you to then UPDATE bogus rows by rowid.

Mufasa
(aka Dave of Sandy, Utah, USA @ 19:32 (26Nov03) GMT, 12:32 (26Nov03) Mountain Time)

 

[SantaMufasa]

...and one more thing: if you allow PH00_CUSTOM_RESERVED to contain 50 blank spaces, then adjust my query to read:

select count(*) from <table>
where [b]([/b]length(trim(translate(PH00_CUSTOM_RESERVED,'^,','^')))<>12 or
length(trim(translate(PH00_CUSTOM_RESERVED,'^0123456789','^')))<>5[b])[/b]
[b]and length(trim(PH00_CUSTOM_RESERVED)) is not null[/b];

Mufasa
(aka Dave of Sandy, Utah, USA @ 19:46 (26Nov03) GMT, 12:46 (26Nov03) Mountain Time)

 

[SantaMufasa]

Sometimes Tek-Tips &quot;coding&quot; rules can produce frustrating results...Here is a resend of the above adjusted code as it should have appeared, without the extraneous bracketed &quot;b&quot;s:

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

Mufasa
(aka Dave of Sandy, Utah, USA @ 19:50 (26Nov03) GMT, 12:50 (26Nov03) Mountain Time)

 

[sem]

I supose that the task was completed in general in the middle of the thread:

select to_date(TRIM(PH00_CUSTOM_RESERVED), 'YY,MM,DD,HH,MI,SS') from phpick00;

The only correction is that in pre-8i releases you should use RTRIM(LTRIM()) instead of TRIM().


Regards, Dima

 

[alexhu]

1 select count(*) from phpick00
2 where length(ltrim(translate(PH00_CUSTOM_RESERVED,'^,','^')))<>12 or
3* length(ltrim(translate(PH00_CUSTOM_RESERVED,'^0123456789','^')))<>5
SQL> /

COUNT(*)
----------
7381

So I have 7381 rows that are incorrectly formatted ?

Alex