On Thu, 14 Nov 2002 09:54:46 -0500, Hapworth, Adam wrote:
> > oh wait. 7-Aug won't work on a date in oracle. Oracle defaults to upper
> > caseing everything so a like on "%7-Aug%" will not match "07-AUG-02" due
> > to the case difference. Try uppering the data you are testing for. do an
> > " '%' || upper($date) || '%' " and see what that does for you.
Only if your instance inherists DD-MON-YYYY from an unmodified default init.ora
Try
alter session set NLS_DATE_FORMAT='DD-Mon-YYYY HH24:MI:SS'
The case of the MON or MONTH case depends upon the case of the (NLS)DATE_FORMAT.
i.e.
select TO_DATE(SYSDATE,'YYYY-Month-DD HH24:MI:SS') from DUAL
select TO_DATE(SYSDATE,'YYYY-Mon-DD HH24:MI:SS') from DUAL
select TO_DATE(SYSDATE,'YYYY-MON-DD HH24:MI:SS') from DUAL
etc.
> >
> > Something like this is what is happening
> >
> > SQL> select * from dual;
> >
> > D
> > -
> > X
> >
> > SQL> select * from dual where dummy like '%x%';
> >
> > no rows selected
> >
> > SQL> select * from dual where dummy like '%'||upper('x')||'%'
s/dummy/upper(dummy)/
you can do this because LIKE does not (in most dbs) use indices.
and so *should* not incurr much more of a performance overhead.
Jacqui
Jacqui Caren, Ingram Group Ltd. [EMAIL PROTECTED]
ph: +44 (0) 1483 8628xx main=00 fax=01 ddi=65
http://www.ig.co.uk/ http://www.sitedirector.org/
http://www.perl.co.uk/
