Re: SQL Problem

>I think if you use to_date() in all the date during the comparation. It
also works.
>(to_date(v.vtran_effective_date) between to_date(z.voidstart) and to_date
(z.voidend))

Wrong!  If the columns are of datatype DATE then you should not use 
TO_DATE on any of them - what is the point of converting a DATE to a DATE?
In fact, since the TO_DATE function expects a VARCHAR2 input, what Oracle 
will do with this is:

(to_date(TO_CHAR(v.vtran_effective_date)) between to_date(TO_CHAR
(z.voidstart)) and to_date(TO_CHAR(z.voidend)))

Now, since your NLS_DATE_FORMAT is set to DD-MON-YY or DD-MON-RR (which is 
really bad, never heard of the Millennium Bug?), then if z.voidstart is 01-
JAN-1999 and z.voidend is 01-JAN-2099, the TO_CHAR will convert both 
to '01-JAN-99'.  You have just lost the century!  

So the answer is:
1) REMOVE all the TO_DATEs from all date columns
2) Change your NLS_DATE_FORMAT to DD-MON-YYYY (or DD-MON-RRRR) so that you 
can see what '99' means.

- Tony

PS Is it just me, or do others not agree that quoting the entire text of 
the discussion so far in every answer is ridiculuous and makes following 
the discussion almost impossible?  The size of the discussion grows 
exponentially!  Why not delete everything but the specific point you are 
answering? 
---
Change your mail options at http://p2p.wrox.com/manager.asp or 
to unsubscribe send a blank email to [email protected].