SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

ORA-01830: date format picture ends before converting entire input string

YoavDec 28 2009 — edited Dec 28 2009

Hi ,
I am runing the following statment and get result back as follow:

SELECT d.table_name, d.partition_name, d.tablespace_name,
  2                 to_date(SUBSTR (d.partition_name,
  3                         2,
  4                         LENGTH (d.partition_name) - 5
  5                         )
  6                 || SUBSTR (d.partition_name,
  7                     LENGTH (d.partition_name) - 3,
  8                     LENGTH (d.partition_name)
  9                     ) ,'YYYYMMDD') part_date
 10                 FROM user_tab_partitions d
 11                 WHERE d.table_name = 'RADIUS_LOG' AND d.partition_name NOT LIKE '%MAX%';

TABLE_NAME                     PARTITION_NAME                 TABLESPACE_NAME                PART_DATE
------------------------------ ------------------------------ ------------------------------ ---------
RADIUS_LOG                     P20091111                      RADIUS_DATA_TS                 11-NOV-09
RADIUS_LOG                     P20091112                      RADIUS_DATA_TS                 12-NOV-09
RADIUS_LOG                     P20091113                      RADIUS_DATA_TS                 13-NOV-09

After adding the part_date to the where clause i am getting : ORA-01830: date format picture .
Why ?

 select t.table_name,t.partition_name, t.tablespace_name,to_date(t.part_date,'dd/mm/rrrr') p_date
  2    from(
  3    SELECT d.table_name, d.partition_name, d.tablespace_name,
  4                  to_date(SUBSTR (d.partition_name,
  5                          2,
  6                          LENGTH (d.partition_name) - 5
  7                          )
  8                  || SUBSTR (d.partition_name,
  9                      LENGTH (d.partition_name) - 3,
 10                      LENGTH (d.partition_name)
 11                      ) ,'YYYYMMDD') part_date
 12                  FROM user_tab_partitions d
 13                  WHERE d.table_name = 'RADIUS_LOG' AND d.partition_name NOT LIKE '%MAX%'
 14         ) t 
 15     where to_date(t.part_date,'dd/mm/rrrr') < to_date(sysdate,'dd/mm/rrrr') - 7;
                || SUBSTR (d.partition_name,
                 *
ERROR at line 8:
ORA-01830: date format picture ends before converting entire input strin

Thanks

Centinul

You have already converted PART_DATE to a date in the subquery. There is no need to convert it again to a date in the outer query. The same follows for SYSDATE. It is already a DATE type do not use TO_DATE() on a DATE, only a character string.

Your second query should probably look something like this instead:

select t.table_name,t.partition_name, t.tablespace_name,t.part_date p_date
    from(
    SELECT d.table_name, d.partition_name, d.tablespace_name,
                  to_date(SUBSTR (d.partition_name,
                          2,
                          LENGTH (d.partition_name) - 5
                          )
                  || SUBSTR (d.partition_name,
                      LENGTH (d.partition_name) - 3,
                      LENGTH (d.partition_name)
                      ) ,'YYYYMMDD') part_date
                  FROM user_tab_partitions d
                  WHERE d.table_name = 'RADIUS_LOG' AND d.partition_name NOT LIKE '%MAX%'
         ) t 
     where t.part_date < sysdate - 7;

HTH!

Karthick2003

15 where to_date(t.part_date,'dd/mm/rrrr') < to_date(sysdate,'dd/mm/rrrr') - 7;

Is part_date is a DATE datatype? If yes then why are you converting it date again.

Just you can do this

where t.part_date < sysdate - 7;

Yoav

Hi,
Thank you for the quick response - But i am stilll getting the same error message:

select t.table_name,t.partition_name, t.tablespace_name,t.part_date p_date
  2      from(
  3      SELECT d.table_name, d.partition_name, d.tablespace_name,
  4                    to_date(SUBSTR (d.partition_name,
  5                            2,
  6                            LENGTH (d.partition_name) - 5
  7                            )
  8                    || SUBSTR (d.partition_name,
  9                        LENGTH (d.partition_name) - 3,
 10                        LENGTH (d.partition_name)
 11                        ) ,'YYYYMMDD') part_date
 12                    FROM user_tab_partitions d
 13                    WHERE d.table_name = 'RADIUS_LOG' AND d.partition_name NOT LIKE '%MAX%'
 14           ) t 
 15       where t.part_date < sysdate - 7;
                  || SUBSTR (d.partition_name,
                   *
ERROR at line 8:
ORA-01830: date format picture ends before converting entire input string

Hoek

SYSDATE is already of DATE datatype, you don't need (or rather: want) to use TO_DATE here, the same goes for T.PART_DATE, which you've already turned into DATE datatype in your inner query.

Yoav

Hi,
Thank you.

I showed in my last post that even when using only :"where t.part_date < sysdate - 7" --> I still get the same error message.
Regarding to column :"part_date" , its just an alias. this column is based on partition_name from user_tab_partitions as you can see in the select statment

Thanks Again

Hoek

Can you post some sample data regarding the partition names?
It looks like something goes wrong when you apply the SUBSTR.

Yoav

Yes, i already post that at the time , i opned this post...

TABLE_NAME    PARTITION_NAME  
------------- ----------------
RADIUS_LOG    P20091111       
RADIUS_LOG    P20091112       
RADIUS_LOG    P20091113

Hoek

Yes, i already post that at the time , i opned this post...

Sorry, you're right, I just recently discovered I can scrolll ;)

It looks like there's a partition name that doesn't comform to the format, resulting in the error.
You should check the values for partition_name.

Centinul

user10634138 wrote:
Yes, i already post that at the time , i opned this post...

TABLE_NAME    PARTITION_NAME  
------------- ----------------
RADIUS_LOG    P20091111       
RADIUS_LOG    P20091112       
RADIUS_LOG    P20091113

If all your partitions conform to PYYYYMMDD, then why have all of the following logic?

SUBSTR (d.partition_name,
                          2,
                          LENGTH (d.partition_name) - 5
                          )
                  || SUBSTR (d.partition_name,
                      LENGTH (d.partition_name) - 3,
                      LENGTH (d.partition_name)
                      )

Have you tried the following instead:

TO_DATE(SUBSTR(D.PARTITION_NAME,2),'YYYYMMDD')

This won't help however if your query is returning partitions names that don't conform to this format.

Yoav

Hi,
Also after applying the recommanded logic , i still get the same error message.
Attached the list of all the partition_name.

  
  alter session set nls_date_format='dd/mm/yyyy';

(Please note that with or without setting the nls_date_format i get the same error message)

1 SELECT d.partition_name,
2 TO_DATE(SUBSTR(D.PARTITION_NAME,2),'YYYYMMDD') part_date
3 FROM user_tab_partitions d
4 WHERE d.table_name = 'RADIUS_LOG'
5 AND d.partition_name NOT LIKE '%MAX%'
6 order by 1 asc

  PARTITION_NAME                 PART_DATE
  ------------------------------ ----------
  P20091108                      08/11/2009
  P20091109                      09/11/2009
  P20091110                      10/11/2009
  P20091111                      11/11/2009
  P20091112                      12/11/2009
  P20091113                      13/11/2009
  P20091114                      14/11/2009
  P20091115                      15/11/2009
  P20091116                      16/11/2009
  P20091117                      17/11/2009
  P20091118                      18/11/2009
  
  PARTITION_NAME                 PART_DATE
  ------------------------------ ----------
  P20091119                      19/11/2009
  P20091120                      20/11/2009
  P20091121                      21/11/2009
  P20091122                      22/11/2009
  P20091123                      23/11/2009
  P20091124                      24/11/2009
  P20091125                      25/11/2009
  P20091126                      26/11/2009
  P20091127                      27/11/2009
  P20091128                      28/11/2009
  P20091129                      29/11/2009
  
  PARTITION_NAME                 PART_DATE
  ------------------------------ ----------
  P20091130                      30/11/2009
  P20091201                      01/12/2009
  P20091202                      02/12/2009
  P20091203                      03/12/2009
  P20091204                      04/12/2009
  P20091205                      05/12/2009
  P20091206                      06/12/2009
  P20091207                      07/12/2009
  P20091208                      08/12/2009
  P20091209                      09/12/2009
  P20091210                      10/12/2009
  
  PARTITION_NAME                 PART_DATE
  ------------------------------ ----------
  P20091211                      11/12/2009
  P20091212                      12/12/2009
  P20091213                      13/12/2009
  P20091214                      14/12/2009
  P20091215                      15/12/2009
  P20091216                      16/12/2009
  P20091217                      17/12/2009
  P20091218                      18/12/2009
  P20091219                      19/12/2009
  P20091220                      20/12/2009
  P20091221                      21/12/2009
  
  PARTITION_NAME                 PART_DATE
  ------------------------------ ----------
  P20091222                      22/12/2009
  P20091223                      23/12/2009
  P20091224                      24/12/2009
  P20091225                      25/12/2009
  P20091226                      26/12/2009
  P20091227                      27/12/2009
  P20091228                      28/12/2009
  P20091229                      29/12/2009
  P20091230                      30/12/2009
  P20091231                      31/12/2009
  P20100101                      01/01/2010
  
55 rows selected.

RADIUS@radiusprd> select t.table_name,t.partition_name, t.tablespace_name,t.part_date p_date
  2      from(
  3      SELECT d.table_name, d.partition_name, d.tablespace_name,
  4                    TO_DATE(SUBSTR(D.PARTITION_NAME,2),'YYYYMMDD') part_date
  5                    FROM user_tab_partitions d
  6                    WHERE d.table_name = 'RADIUS_LOG' AND d.partition_name NOT LIKE '%MAX%'
  7           ) t 
  8       where t.part_date < sysdate - 7;
                  TO_DATE(SUBSTR(D.PARTITION_NAME,2),'YYYYMMDD') part_date
                          *
ERROR at line 4:
ORA-01830: date format picture ends before converting entire input string

Hoek

Unable to reproduce ora-01830, but I don't have your data and/or partitions:

SQL>with t as (
  2  select 'P20091108' col from dual union all
  3  select 'P20091109' from dual union all
  4  select 'P20091110' from dual union all 
... etc etc 
 53  select  'P20091229'  from dual union all
 54  select  'P20091230'  from dual union all
 55  select  'P20091231'  from dual union all
 56  select  'P20100101'  from dual
 57  )
 58  select * 
 59  from ( select col
 60         ,      to_date(substr(col, 2), 'yyyymmdd') part_date
 61         from   t
 62        )
 63  where part_date < sysdate - 7;

COL       PART_DATE
--------- ----------
P20091108 08/11/2009
P20091109 09/11/2009
P20091110 10/11/2009
etc etc ...
P20091217 17/12/2009
P20091218 18/12/2009
P20091219 19/12/2009
P20091220 20/12/2009
P20091221 21/12/2009

44 rijen zijn geselecteerd.

However, perhaps this makes a difference:

select table_name
,      partition_name
,      tablespace_name
,      to_date(substr(partition_name,2),'yyyymmdd') part_date
from ( select d.table_name
       ,      d.partition_name
       ,      d.tablespace_name
       from   user_tab_partitions d
       where  d.table_name = 'RADIUS_LOG' 
       and    d.partition_name not like '%MAX%'
     ) t 
where to_date(substr(partition_name,2),'yyyymmdd') < sysdate - 7;

Frank Kulash

Hi,

First, check that all of the partition_names really have valid formats:

SELECT	partition_name
FROM	user_tab_partitions
WHERE	table_name	= 'RADIOUS LOG'
AND	partition_name	NOT LIKE '%MAX%'
AND	TRANSLATE ( SUBSTR (partition_name, 2)
		  , '012345678'
		  , '999999999'
		  ) != '99999999'
;

The query above should produce no rows.

If it indeed does produce no rows, then it looks like the optimizer is applying the main WHERE caluse:

where t.part_date < sysdate - 7

before it is is applying the sub-query WHERE clause:

WHERE d.table_name = 'RADIUS_LOG' AND d.partition_name NOT LIKE '%MAX%'

I think I've seen people get around that by including ROWNUM in the sub-query. for example:

 select t.table_name,t.partition_name, t.tablespace_name,t.part_date p_date
 from(
      SELECT d.table_name, d.partition_name, d.tablespace_name,
                   TO_DATE(SUBSTR(D.PARTITION_NAME,2),'YYYYMMDD') part_date
		   , ROWNUM  AS rnum						-- Added
                   FROM user_tab_partitions d
                   WHERE d.table_name = 'RADIUS_LOG' AND d.partition_name NOT LIKE '%MAX%'
      ) t 
  where t.part_date < sysdate - 7;

Or you may have to actually reference rnum in the main query, like this:

 select t.table_name,t.partition_name, t.tablespace_name,t.part_date p_date
 from(
      SELECT d.table_name, d.partition_name, d.tablespace_name,
                   TO_DATE(SUBSTR(D.PARTITION_NAME,2),'YYYYMMDD') part_date
		   , ROWNUM  AS rnum						-- Added
                   FROM user_tab_partitions d
                   WHERE d.table_name = 'RADIUS_LOG' AND d.partition_name NOT LIKE '%MAX%'
      ) t 
  where t.part_date < sysdate - 7
  and	rnum	    >= 1							-- Added
;

You can also try doing the TO_DATE in a CASE expression, like this:

 select t.table_name,t.partition_name, t.tablespace_name,t.part_date p_date
 from(
      SELECT d.table_name, d.partition_name, d.tablespace_name,
                   CASE
			WHEN  d.table_name	= 'RADIUS_LOG' 
			AND   d.partition_name	NOT LIKE '%MAX%'
			THEN  TO_DATE (SUBSTR (D.PARTITION_NAME,2),'YYYYMMDD')
		   END     AS  part_date
                   FROM user_tab_partitions d
                   WHERE d.table_name = 'RADIUS_LOG' AND d.partition_name NOT LIKE '%MAX%'
      ) t 
  where t.part_date < sysdate - 7;

or even:

 select t.table_name,t.partition_name, t.tablespace_name,t.part_date p_date
 from(
      SELECT d.table_name, d.partition_name, d.tablespace_name,
                   CASE
			WHEN  d.table_name	!= 'RADIUS_LOG' 
			OR   d.partition_name	LIKE '%MAX%'
			THEN  NULL
			ELSE  TO_DATE (SUBSTR (D.PARTITION_NAME,2),'YYYYMMDD')
		   END     AS  part_date
                   FROM user_tab_partitions d
                   WHERE d.table_name = 'RADIUS_LOG' AND d.partition_name NOT LIKE '%MAX%'
      ) t 
  where t.part_date < sysdate - 7;

Sorry, I don't know how to reproduce the problem myself.

Solomon Yakobson

user10634138 wrote:
Hi,
Also after applying the recommanded logic , i still get the same error message.

This is another example of view dual nature. By definition view is a logical table. However we all understand that following such definition will often result in terrible performance. Therefore, it is optimizer who decides to treat a view as a logical query or as a stored query. I am almost positive (and you can run explain plan to verify), in your case in-line view was treated as a stored query. As a result, statement got rewritten into something like

select  table_name,
        partition_name,
        tablespace_name,
        TO_DATE(SUBSTR(PARTITION_NAME,2),'YYYYMMDD') part_date
  FROM  user_tab_partitions d
  WHERE table_name = 'RADIUS_LOG'
    AND partition_name NOT LIKE '%MAX%'
    AND TO_DATE(SUBSTR(PARTITION_NAME,2),'YYYYMMDD') < sysdate - 7
/

Now it all depends in which order predicates are applied. And it looks like TO_DATE(SUBSTR(PARTITION_NAME,2),'YYYYMMDD') < sysdate - 7 is applied before table_name = 'RADIUS_LOG' and/or partition_name NOT LIKE '%MAX%' which results in ORA-01830. Try materializing in-line view:

select  t.table_name,
        t.partition_name,
        t.tablespace_name,
        t.part_date p_date
  from  (
         SELECT  d.table_name,
                 d.partition_name,
                 d.tablespace_name,
                 TO_DATE(SUBSTR(D.PARTITION_NAME,2),'YYYYMMDD') part_date,
                 ROWNUM rnum
           FROM  user_tab_partitions d
           WHERE d.table_name = 'RADIUS_LOG'
             AND d.partition_name NOT LIKE '%MAX%'
        ) t 
  WHERE t.part_date < sysdate - 7;

SY.

1 - 13

Locked Post

New comments cannot be posted to this locked post.

Locked on Jan 25 2010

Added on Dec 28 2009

13 comments

10,845 views

SQL & PL/SQL

ORA-01830: date format picture ends before converting entire input string

Comments

Post Details