Home » SQL & PL/SQL » SQL & PL/SQL » date format picture ends before converting entire input string (10.2.0.3)
date format picture ends before converting entire input string [message #358740] Wed, 12 November 2008 06:04 Go to next message
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 #358746 is a reply to message #358740] Wed, 12 November 2008 06:21 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Ie,

TO_DATE(SUBSTR(PARTITION_NAME,4) is not compatible to the date format , re-check the format.

Smile
Rajuvan.
Re: date format picture ends before converting entire input string [message #358748 is a reply to message #358746] Wed, 12 November 2008 06:35 Go to previous messageGo to next message
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 #358753 is a reply to message #358748] Wed, 12 November 2008 06:45 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> select TO_DATE(SUBSTR('TM_2008_06_11',4),'YYYY_MM_DD') from dual;
TO_DATE(SUBSTR('TM_
-------------------
11/06/2008 00:00:00

1 row selected.

Strange, I don't have the same result.
(See what I mean? Wink )

Regards
Michel
Re: date format picture ends before converting entire input string [message #358757 is a reply to message #358740] Wed, 12 November 2008 07:02 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

I hope what you are selecting is a some sample rows.

\Most probably you will get the same error for

SELECT coun(TO_DATE(SUBSTR(PARTITION_NAME,4),'YYYY_MM_DD'))
                 from user_tab_partitions 
                 where table_name = 'Adi_METRIC' ;


ie, Some of the Partition names not compatible(Not all)

Smile
Rajuvan.
Re: date format picture ends before converting entire input string [message #358761 is a reply to message #358748] Wed, 12 November 2008 07:11 Go to previous messageGo to next message
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

Re: date format picture ends before converting entire input string [message #358769 is a reply to message #358761] Wed, 12 November 2008 07:46 Go to previous messageGo to next message
adit_me1
Messages: 49
Registered: October 2007
Location: BANGALORE
Member
Awesome.... Smile

your query worked !!!
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


this is unbelievable... Thanks for the link.. I will go through it Smile
Re: date format picture ends before converting entire input string [message #358780 is a reply to message #358740] Wed, 12 November 2008 08:33 Go to previous messageGo to next message
cookiemonster
Messages: 12403
Registered: September 2008
Location: Rainy Manchester
Senior Member
Don't think you need rownum though Cool
Re: date format picture ends before converting entire input string [message #358803 is a reply to message #358780] Wed, 12 November 2008 09:32 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
It is there for a reason. Try to turn on the 10053 trace and see the final query which gets executed behind the scenes by having with and without the rownum. Assumption is query_rewrite_enabled parameter is set to true.

Regards

Raj
Re: date format picture ends before converting entire input string [message #358990 is a reply to message #358803] Thu, 13 November 2008 03:20 Go to previous messageGo to next message
adit_me1
Messages: 49
Registered: October 2007
Location: BANGALORE
Member
Try to turn on the 10053 trace and see the final query which gets executed behind the scenes by having with and without the rownum. Assumption is query_rewrite_enabled parameter is set to true.
what does this mean? I am a newbie to Oracle... can you explain or give me a link to read on this?

[Updated on: Thu, 13 November 2008 03:40] by Moderator

Report message to a moderator

Re: date format picture ends before converting entire input string [message #359002 is a reply to message #358990] Thu, 13 November 2008 04:55 Go to previous message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Google is your friend. Search for 10053 trace in google and you will find loads of information. To start with check this link. It explains briefly what 10053 trace is about ?

http://www.psoug.org/reference/trace_tkprof.html

Regards

Raj
Previous Topic: What should the column datatype be?
Next Topic: Output of two queries in single row having complex relationship
Goto Forum:
  


Current Time: Sat Dec 03 15:47:40 CST 2016

Total time taken to generate the page: 0.04071 seconds