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

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.

"Billy Verreynne" <vslabs_at_onwe.co.za> wrote in message news:b5rli7$qcq$1_at_ctb-nnrp2.saix.net...
> David G Stevens wrote:
>
> > For example:
> >
> > WHERE TO_DATE(TO_CHAR(YEAR)||'.'||TO_CHAR(MONTH)||'.01,'YYYY.MM.DD') >=
> > (d{'2000-05-01'})
>
> Damn. But that is ugly code.
>
> You know that slapping functions on columns in the WHERE clause result in
> full table scans, unless those are function indexes.
>
> The right way to treat dates is as, no big surpise really, dates in the
> database. I.e. using the DATA data type. Not breaking up the column into
> pieces.
>
> The right way to use date columns via WHERE clauses is to apply a date
> conversion to the literal, i.e.
> WHERE date_column = TO_DATE( literal, dateformat )
>
> The content and format of the literal is dependant on the client
> application. It is up to the application to support the local date format
> of that PC and ensure the correct conversion of that format in the WHERE
> clause. e.g.
> WHERE date_column = TO_DATE( '2000-05-01', 'yyyy-mm-dd' )
>
> Also, if you do not specify the day, it defaults to day one, e.g.
> WHERE date_column = TO_DATE( '2000-05', 'yyyy-mm' )
> /* will convert to date 2000/05/01 */
>
>
> --
> Billy
Received on Wed Mar 26 2003 - 13:51:27 CST