Re: Date field Problem

Jerry,

A number of ways you can do this, eg

if the table has these values:

STRG                 TO_CHAR(I_DATE,'DD-MON-YYHH24:MI:SS')
-------------------- ---------------------------------------------------------------------------
DATE 1               29-FEB-96 09:36:47
DATE 2               07-MAR-96 09:42:21

THEN query:

   select * from vtest
    where TRUNC(to_date(to_char(i_date,'MM DD YYYY'),'MM DD YYYY')) =

          to_date('02 29 1996','MM DD YYYY');

RESULTS:

        STRG                 I_DATE
        -------------------- ---------
        DATE 1               29-FEB-96

Other combinations of the to_date 7 to_char functions can also be used to produce the results. Note Oracle has the default date storage format of   'DD-MON-YY' and if you do not convert it to the format you are comparing against the results will not match (there is a implied string comparison here).

Hope this helps.

Vikram.

--
Vikram Goel                                 Motorola email: vgoel_at_pts.mot.com
 Sr. Oracle DBA - Consultant
Aerotek Inc.                                My email:  vgoel_at_emi.net

Motorola Info:
Mail Stop 39, Room S1014
1500 Gateway Blvd,
Boynton Beach, FL 33426 


In article <4gl3q8$e9l_at_masala.cc.uh.edu>, ymeng_at_Bayou.UH.EDU (Jerry Xia) writes:


>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