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

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