Re: Date field Problem

Shawn Belaire - Sun Ottawa SSE <shawn_at_canada.sun.com> wrote:
>Jerry Xia wrote:
>>
>> I am using Oracle7.1 database and I have one date datatype column
>> in a table. When I use "where date_column = TO_DATE('01-JAN-01')"
>> in my query, it just works fine. But if I use
>> "where date_column = TO_DATE('01 01 0001', 'mm dd yyyy')", it
>> returns no rows. TO_DATE('01/01/01', 'mm/dd/yy') format doesn't
>> work either.
>>
>> Does anyone know what is the problem? Is something wrong with the data
>> stored in the table?
>>
>> Thank in advance.
>>
>> Jerry
>
>--
>Yes you must use trunc(date_field) with your query because date fields
>are stored to a greater length than dd-mm-yy
>
>
>regards
>
>Shawn Belaire Sun Microsystems of Canada Ph: (613)
>787-4278
>SunService SSE 350 Albert Street, Suite 1700
> Ottawa, Ontario, K1R 1A4, Canada
>shawn.belaire_at_Canada.sun.com

Another possibiltiy is that the first query probably used a century value of '19' giving the resultant year of '1901'.

To test this, trying specifying the year 1901 in your other query. If you still have problems, try a select that does a to_char against the database column and match it against the date represented in a character format.

-- 

David M. Taulbee
TRW Vehicle Safety Systems
dtaulbee_at_po.michsb.trw.com