Re: Please Help: Dates in Oracle

Mike wrote:
>
> Hello,
>
> I'm trying to write a SQL Passthrough query to an Oracle database in access.
> I seem to be having trouble querying by a date field. Here's my entire SQL
> statement:
>
> SELECT *
> FROM HREI.DEPARTMENT_CODE
> WHERE EXTRACT_DATE = #12/13/97#;
>
> HREI.DEPARTMENT_CODE is the table
>
> EXTRACT_DATE is a field containing date formatting like this:
> 10/4/97
>
> I've played arround with other fields in the database and I can query fine.
> It's with the date field that I'm having problems. Am I using the "#" signs
> correctly?
>
> Any help would be much appreciated. Thanks
>
> Mike.

 Mike -

Following is quoted from "ORACLE: The Complete Reference": 'DATE is a standard ORACLE datatype to store date and time data. Standard date format is 01-APR-95. A DATE column may contain a date and time between January 1, 4712 B.C. and December 31 ,4712 A.D.' The resolution of a DATE datatype is one second. Oracle stores all dates in its own proprietary internal format, irrespective of any formatting that may be done implicitly for exterbal display. Since you are using SQLPASSTHROUGH, you are simply handing to Oracle Server the text of the SELECT statement and asking it to parse and interpret it. But Oracle doesn't recognize "#12/13/97#" and doesn't know how to handle it. You have two choices. First, you can pass the date in a format Oracle understands:

        EXTRACT_DATE = '13-Dec-97';

The single quotation marks are a mandatory part of the text string. Alternatively, you could use the Oracle conversion function TO_DATE, e.g.

        EXTRACT_DATE = TO_DATE('12/13/97', 'MM/DD/YY'); Again, the single quotation marks are mandatory. Either of these approaches should work.

• Mark