This content has been marked as final. Show 5 replies
Could it be that have table partions which name is longer than 11 characters?
This part of your query works fine:
select to_char(to_date(substr('PAR20080302',4),'YYYYMMDD'),'DY') pardate from dual;Unless you encounter a partion with a longer name:
select to_char(to_date(substr('PAR20080302_more',4),'YYYYMMDD'),'DY') pardate from dual;Depending of the nature of the other partion names you could change your arguments to substr:
ORA-01830: date format picture ends before converting entire input string
select to_char(to_date(substr('PAR20080302_more',4,8),'YYYYMMDD'),'DY') pardate from dual;Regards
The behaviour you have mentioned in something strange. So I was just interested in it. Do let me know if you have solved the issue. If yes then how and if no then could you please post the exact output and query.
I have also noticed that in where clause you have used table_name like 'PAR', but it should be something like table_name like 'PAR%'. You might have mistyped this while posting the query and would have used it correctly in your implementation. Correct?
this is a very common mistake
the function to_date is possibly executed for every partition, the where condition is evaluated after the to_date.
select to_char(to_date(substr(partition_name,4),'YYYYMMDD'),'DY') pardate from dba_tab_partitions where table_name like 'PAR%'
you probably want
rownum should prevent view merging.
select to_char(to_date(substr(partition_name,4),'YYYYMMDD'),'DY') pardate from ( select rownum, partition_name from dba_tab_partitions where table_name like 'PAR%' )
Thanks for the replies. When I get a chance I'll test them out and update
I'm trying to run sql to grab just the weekend partitions on table AR_ATB_DATA
We have table partitions named as such
When I run this below query, I am getting the following error:
select to_char(to_Date(substr(partition_name,6),'YYYY-MM-DD'),'DD-MON-YYYY') from (select partition_name from dba_tab_partitions where table_name='AR_ATB_DATA' and table_owner='ADBA' order by 1 desc) where rownum < 2
ERROR at line 1:
ORA-01841: (full) year must be between -4713 and +9999, and not be 0
Can you please help me how to avoid this error