Home » SQL & PL/SQL » SQL & PL/SQL » to_date comparison problem
to_date comparison problem [message #301139] Tue, 19 February 2008 05:28 Go to next message
tangomann
Messages: 6
Registered: February 2008
Junior Member
Hi everybody. I am facing a strange beahviour of some select at the moment. In order to encircle the problem I have reduced the query to the absolute minimum. Maybe anyone has any idea?

I am having a view which (besides others) has a column of type varchar. Its data is dates in format 'DD.MM.YYYY'.

Now trying to query the view I am facing this behaviour:

1 -
select * from my_view
where 
to_date('18.02.2008', 'DD.MM.YYYY') <= to_date(shipment_date, 'DD.MM.YYYY')
and to_date('24.02.2008', 'DD.MM.YYYY') >= to_date(shipment_date, 'DD.MM.YYYY')

--> here I get an error (ORA-01830: date format picture ends before converting entire input string)

2 -
select to_date(shipment_date, 'DD.MM.YYYY') from my_view

--> works fine. any varchar in the column is parsable

So i reduced it more and began to use sysdate:

3 -
select * from my_view where trunc(sysdate) >= to_date(shipment_date, 'DD.MM.YYYY')

--> does not work (same error again [ORA 01830])

4 -
select * from my_view where trunc(sysdate) >= to_date('18.02.2008', 'DD.MM.YYYY')

--> works. very strange!

Finally I tried this (and could not believe it)

5 -
select * from my_view where
to_date(shipment_date, 'DD.MM.YYYY')  <= to_date(shipment_date, 'DD.MM.YYYY')

--> ERROR (ORA 01830)

To finalize the mystery, this problem only occurs on the production databse. On the development db everything works fine. They say it the same version of db (10g) and both have same entries in nls_* tables.


Thanks for your help.
Re: to_date comparison problem [message #301142 is a reply to message #301139] Tue, 19 February 2008 05:40 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Have you fetched ALL columns in example 2?

If you use some GUI then perhaps only the first X rows are fetched, and the one with the problem is further down.

Can you do a

select * from my_view where length(shipment_date) != 10;


and see if it returns anything?
Re: to_date comparison problem [message #301152 is a reply to message #301139] Tue, 19 February 2008 06:03 Go to previous messageGo to next message
tangomann
Messages: 6
Registered: February 2008
Junior Member
Thanks for you quick answer.

Yes, I have fetched all data from ex. 2.

I even wrote some PL/SQL to check the data. (Please excuse my poor pl/sql skills Smile)

This should check if any data in the column is parsable and if a comparison to another date is possible. The script ran without any output from the catch block.

DECLARE
       cursor suchefehler is
       select shipment_date
       from my_view;

       sd varchar(250);
       d date;
       tmp varchar(1);
BEGIN
   dbms_output.enable(100000);
 
  DBMS_OUTPUT.put_line( 'Start ...' );
 
  BEGIN
  
  OPEN suchefehler;

  loop
    FETCH suchefehler into sd;
    exit when suchefehler%NOTFOUND;

    
    d := trunc(to_date(sd,'DD.MM.YYYY'));
    
    if (d >= trunc(to_date('18.02.2008', 'DD.MM.YYYY'))) then
      -- dbms_output.put_line ('parsed (after):' || sd || ' to ' || d);
      tmp := 'a';
    end if;  
    
    if (d <= trunc(to_date('24.02.2008', 'DD.MM.YYYY'))) then
      -- dbms_output.put_line ('parsed (before):' || sd || ' to ' || d);
      tmp := 'b';
    end if;  
    
  end loop;

  CLOSE suchefehler;
  
  EXCEPTION
     WHEN OTHERS THEN 
          dbms_output.put_line ('Error:' || sqlerrm || ' while parsing ' || sd);
          GOTO weiter;
  END;
  
  <<weiter>>
  DBMS_OUTPUT.put_line( '.... end!' );
  
 
END;

Re: to_date comparison problem [message #301157 is a reply to message #301152] Tue, 19 February 2008 06:18 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Is your result-set from the view only a subset of the original table where shipment_date comes from?
Then it could be possible that the to_date is pushed into the view, and causes a conflict on records in the original table that would not make it into the view.

In fact, this is the same idea as ThomasG suggested, only one level deeper.

[Updated on: Tue, 19 February 2008 06:19]

Report message to a moderator

Re: to_date comparison problem [message #301161 is a reply to message #301139] Tue, 19 February 2008 06:41 Go to previous messageGo to next message
tangomann
Messages: 6
Registered: February 2008
Junior Member
I have executed a query on the joined table (where the shipment_date comes from). All data in that table was parsable with to_date().

Any other ideas?
Re: to_date comparison problem [message #301170 is a reply to message #301161] Tue, 19 February 2008 07:15 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Any other to_dates in the view? (same reason)
Re: to_date comparison problem [message #301172 is a reply to message #301170] Tue, 19 February 2008 07:21 Go to previous messageGo to next message
MarcS
Messages: 312
Registered: March 2007
Location: Antwerp
Senior Member
Frank wrote on Tue, 19 February 2008 14:15
Any other to_dates in the view? (same reason)


Or in the VIEW-definition for that matter.

@tangomann: If it's not too complex, why not post the VIEW-definition as well
Re: to_date comparison problem [message #301173 is a reply to message #301139] Tue, 19 February 2008 07:23 Go to previous messageGo to next message
tangomann
Messages: 6
Registered: February 2008
Junior Member
No, there are no (other) dates in the view.

Actually there are no dates in the view at all. All data (except the one key) is varchar. I am performaing the to_date() when querying the view not when creating it.
Re: to_date comparison problem [message #301176 is a reply to message #301173] Tue, 19 February 2008 07:26 Go to previous messageGo to next message
MarcS
Messages: 312
Registered: March 2007
Location: Antwerp
Senior Member
tangomann wrote on Tue, 19 February 2008 14:23
No, there are no (other) dates in the view.

Actually there are no dates in the view at all. All data (except the one key) is varchar. I am performaing the to_date() when querying the view not when creating it.


But creating the view doesn't perform the TO_DATE(), only when you query the view.

Could you post the VIEW-definition?

(Hoping that it's not a huge one Wink )
Re: to_date comparison problem [message #301179 is a reply to message #301173] Tue, 19 February 2008 07:29 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Or just "desc my_view" removing all irrelevant lines.

Regadrs
Michel
Re: to_date comparison problem [message #301183 is a reply to message #301179] Tue, 19 February 2008 07:34 Go to previous messageGo to next message
MarcS
Messages: 312
Registered: March 2007
Location: Antwerp
Senior Member
Michel Cadot wrote on Tue, 19 February 2008 14:29
Or just "desc my_view" removing all irrelevant lines.

Regadrs
Michel



I'm afraid we need the underlying query as well.
Re: to_date comparison problem [message #301185 is a reply to message #301139] Tue, 19 February 2008 07:54 Go to previous messageGo to next message
tangomann
Messages: 6
Registered: February 2008
Junior Member
Thanks for all your answers. Im afraid I made a mistake in my last posting. It seems like you have found the error. When analysing the data from the "base" table we did not fetch all the data. (Actually it was not me doing this because I have no access to the production-db Wink) In the very last lines there some 'dates' not parsable.

Sorry for that. And thank you very much for your help!


P.S. I always imagined a view as something like a temporary table. Could not imagine that functions are executed on all underlying data. Shocked
Re: to_date comparison problem [message #301189 is a reply to message #301185] Tue, 19 February 2008 08:02 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Good to hear you found it.
Next step is how to create an access-path to exclude those rows before the to_date is let loose on them!

Don't blame yourself, this is one thing I find very few developers (even the more experienced) are aware of.
Actually, it was the series of tests you performed that made it clear that this more or less _had_ to be the error.

[Updated on: Tue, 19 February 2008 08:04]

Report message to a moderator

Re: to_date comparison problem [message #301195 is a reply to message #301189] Tue, 19 February 2008 08:32 Go to previous messageGo to next message
tangomann
Messages: 6
Registered: February 2008
Junior Member
Frank wrote on Tue, 19 February 2008 08:02

Next step is how to create an access-path to exclude those rows before the to_date is let loose on them!



Actually the admins have just "corrected" the bad values. Can you provide me any link where I can find more about that topic? Are you talking about 'hints'? I will start to educate in this topic then.
Re: to_date comparison problem [message #303312 is a reply to message #301139] Thu, 28 February 2008 19:14 Go to previous message
dr.s.raghunathan
Messages: 540
Registered: February 2008
Senior Member
hi,

is it a tough task? Just rtrim before convert and compare?

yours
dr.s.raghunathan
Previous Topic: compiled with errors
Next Topic: Self join with inner join followed by a full outer join
Goto Forum:
  


Current Time: Thu Dec 08 08:42:53 CST 2016

Total time taken to generate the page: 0.17556 seconds