Re: Problem with Date in WHERE clause using Oracle via ODBC

"David G Stevens" <David_at_les-arbres.co.uk> wrote in news:b5t0c1$bt2$1_at_newsg4.svr.pol.co.uk:

> As Billy mentions - of course this would be done in an ideal world,
> but...
>
> I have no control over the database structure or how it got that way.
> My question again is can anyone suggest what could have changed (ODBC
> driver version or db settings) that would cause the "ugly" code to
> stop working when it had functioned perfectly well for some time.
>
> Once again - any answers appreciated.

Hello David, you'll have to give us a clue - stop working? Does it give any error? Stop returning rows, return fewer rows, more rows, or just plain different rows.

> > > For example:
> > >
> > > WHERE TO_DATE(TO_CHAR(YEAR)||'.'||TO_CHAR(MONTH)||'.01,'YYYY.MM.DD')

>=


> > > (d{'2000-05-01'})

I don't know much about ODBC but you have a function d which takes a string and appears to return a date, but no format is given. So a change to nls_date_format at the db or session level could stop it behaving correctly.

But yes it is ugly code, can't the d function be changed to to_date('2000-05-01','yyyy-mm-dd') that would fix it.

Hth
Martin Received on Wed Mar 26 2003 - 19:42:05 CST