date format picture ends before converting entire input string [message #358740] |
Wed, 12 November 2008 06:04  |
adit_me1
Messages: 49 Registered: October 2007 Location: BANGALORE
|
Member |
|
|
Hello all,
I have a partitioned table and the partition names are named on based on the date for which they carry data.
For example partition "TM_2007_03_01" containins data only for 01-mar-07.
I wish to have a look at all those partitions that are more than 100 days old.
I am able to convert the partition names to a valid date and query for all the partitions using the following query..
SELECT PARTITION_NAME,TO_DATE(SUBSTR(PARTITION_NAME,4),'YYYY_MM_DD')
from user_tab_partitions
where table_name = 'Adi_METRIC'
but when i add the clause for the date range..
SELECT PARTITION_NAME,TO_DATE(SUBSTR(PARTITION_NAME,4),'YYYY_MM_DD')
from user_tab_partitions
where table_name = 'Adi_METRIC'
[B]and TO_DATE(SUBSTR(PARTITION_NAME,4),'YYYY_MM_DD') < sysdate - 100[/B]
it gives the error - date format picture ends before converting entire input string
Please advice as to where I am going wrong and how I can correct my query.
|
|
|
|
Re: date format picture ends before converting entire input string [message #358748 is a reply to message #358746] |
Wed, 12 November 2008 06:35   |
adit_me1
Messages: 49 Registered: October 2007 Location: BANGALORE
|
Member |
|
|
No,
the format is good. If it was not, then why would the first query return me the results for the 2nd column?
SELECT PARTITION_NAME,TO_DATE(SUBSTR(PARTITION_NAME,4),'YYYY_MM_DD')
from user_tab_partitions
where table_name = 'Adi_METRIC'
PARTITION_NAME TO_DATE(SUBSTR(PARTITION_NAME,4),'YYYY_MM_DD')
TM_2008_06_11 6/11/2008
TM_2008_06_10 6/10/2008
TM_2008_06_09 6/9/2008
TM_2008_06_08 6/8/2008
TM_2008_06_07 6/7/2008
TM_2008_06_06 6/6/2008
|
|
|
|
|
Re: date format picture ends before converting entire input string [message #358761 is a reply to message #358748] |
Wed, 12 November 2008 07:11   |
S.Rajaram
Messages: 1027 Registered: October 2006 Location: United Kingdom
|
Senior Member |
|
|
I know I am posting you a link which points to a different error but the reason behind both these errors are the same.
http://asktom.oracle.com/pls/asktom/f?p=100:11:8732259892480315::::P11_QUESTION_ID:1299201885045
Quote: |
SELECT PARTITION_NAME,TO_DATE(SUBSTR(PARTITION_NAME,4),'YYYY_MM_DD')
from user_tab_partitions
where table_name = 'Adi_METRIC'
|
Quote: |
SELECT PARTITION_NAME,TO_DATE(SUBSTR(PARTITION_NAME,4),'YYYY_MM_DD')
from user_tab_partitions
where table_name = 'Adi_METRIC'
and TO_DATE(SUBSTR(PARTITION_NAME,4),'YYYY_MM_DD') < sysdate - 100
|
These two queries are two different things in other words you are comparing apples with oranges. In your first query your filter criteria is only on the table_name and then you apply the formatting on the partition_name. In your second query you are having two filter criteria. You are at the mercy of oracle in the order in which the filters are applied. You don't have any control over that. Hence you get the error. If you read the link I have posted you will get more clarity about what I mean regarding the filter criteria.
If you change the query in the following fashion it should work.
SELECT PARTITION_NAME,TO_DATE(SUBSTR(PARTITION_NAME,4),'YYYY_MM_DD')
from (
select partition_name, rownum from user_tab_partitions
where table_name = 'Adi_METRIC')
where
TO_DATE(SUBSTR(PARTITION_NAME,4),'YYYY_MM_DD') < sysdate - 100
Hope this helps.
Regards
Raj
[Updated on: Wed, 12 November 2008 07:17] Report message to a moderator
|
|
|
|
|
|
|
|