hi,



   For that u should again wrap the TO_CHAR function with TO_DATE 

function, like this:



    select * from tablename where 

TO_DATE(TO_CHAR(somedate,'DD/MM/YYYY'))>TO_DATE(TO_CHAR(someotherdate,'DD

/MM/YYYY')



Hope this helps,



N.T.GOPALAKRISHNAN

  ----- Original Message -----

  From: avance@c...

  To: professional vb

  Sent: Thursday, May 24, 2001 2:33 PM

  Subject: [pro_vb] Re: Oracle 8i SQL with date/time fields





  Will that work for "greater than" and "less than" queries?





  -----Original Message-----

  From: Gopalakrishnan [mailto:ntg@s...]

  Sent: 24 May 2001 05:40

  To: professional vb

  Subject: [pro_vb] Re: Oracle 8i SQL with date/time fields





  hi,



   Change the TO_DATE to TO_CHAR and you should see it work.



  Hope this helps,



  N.T.GOPALAKRISHNAN

    ----- Original Message -----=3D20

    From: avance@c...=3D20

    To: professional vb=3D20

    Sent: Wednesday, May 23, 2001 7:12 PM

    Subject: [pro_vb] Oracle 8i SQL with date/time fields





    Hi,



    I wonder can anybody help me out.  I am querying an oracle 8i 

database =3D

  from

    VB using OLEDB 8.1.6 and (sql displayed below) it fails to return 

any =3D

  rows

    when querying on a date.  This is weird, as it returns a row within 

=3D

  TOAD (a

    SQL editor tool for Oracle).  Has anybody been through this problem 

=3D

  before

    perhaps?  I hope!



    DateRaised contains both the Date and Time.  The query would work if 

=3D

  it

    contained the date only.  I thought my using TO_DATE on the column 

=3D

  also that

    this would rectify things.  Guess not.



    Alastair.



    SELECT   Despatch.Status As StatusID,

      DESPATCH.DESPATCH_ID,

      'D' || DESPATCH.DESPATCH_ID As DESPATCH_NO,

      OSNO.OSNO,

      DESPATCH.STUDYNO,

      PROJECT.PROJECTNO,

      DESPATCH.CUSTOMER_REF,

      LOOKUP_STATUS.STATUS,

      DESPATCH.DATE_RAISED,

      DECODE(DESPATCH.GENERIC, 0, 'NO', 1, 'YES', 'NO') IS_GENERIC

    FROM   MOSES.DESPATCH ,

      MOSES.OSNO,

      MOSES.PROJECT ,

      MOSES.LOOKUP_STATUS

    WHERE   ( (DESPATCH.OSNO_ID =3D3D OSNO.OSNO_ID) AND

      (OSNO.PROJECT_ID =3D3D PROJECT.PROJECT_ID) AND

      (DESPATCH.STATUS =3D3D LOOKUP_STATUS.STATUS_ID)    AND

      (DESPATCH.DEACTIVATE IS NULL) AND

      (PROJECT.DEACTIVATE IS NULL) AND

      (OSNO.DEACTIVATE IS NULL) AND

      DESPATCH.STATUS <> 7  AND

      DESPATCH.STATUS <> 8  AND

      DESPATCH.STATUS <> 9  AND

      DESPATCH.STATUS <> 10  AND

      DESPATCH.STATUS <> 11)  AND

      TO_DATE(despatch.date_raised,'DD/MM/YY') =3D3D

    TO_DATE('03-MAY-2001','DD/MM/YY')