2 Replies Latest reply: Nov 29, 2012 12:41 PM by salute-Salem RSS

    quering with sysdate function

    salute-Salem
      hi all ,
      there is a strange thing right here ,
      i set the where clause for my database block to , ( test_date = sysdate )

      then , query caused no records to be retrieved , and i am sure that the table has records with sysdate date .

      why does this happen ?

      even if i queried the table in the isqlplus ,
      select test_date from patients ; -- then queries with sysdate date are retrieved , but if i write
      select test_date from patients where test_date = sysdate . -- there is nothing to be retrieved .?

      help ?

      even i created a new table tt with one column(cc) , and inserted the value sysdate in it ,
      when i select sysdate from dual then it works 27-NOV-12 but
      when i select dd from cc where dd = sysdate then no rows selected

      Edited by: semsem on Nov 27, 2012 11:33 AM
        • 1. Re: quering with sysdate function
          user346369
          sysdate contains both date and current time elements, down to seconds. You will never find a match on sysdate, unless you hit the query at exactly the right time that matches some record in your table.

          Set your where clause to:

          ( trunc (test_date) = trunc (sysdate) )

          The "trunc" truncates (removes) the time element from your date values before doing the conditional test.
          when i select sysdate from dual then it works 27-NOV-12
          Do this in SQL Plus:
          alter session set nls_date_format='mm-dd-yyyy hh24:mi:ss';
          That will enable you to see the true date and time contained in your date datatypes. Here is an example:
          SQL> select sysdate from dual;
          
          SYSDATE
          ---------
          27-NOV-12
          
          SQL> alter session set nls_date_format='mm-dd-yyyy hh24:mi:ss';
          
          Session altered.
          
          SQL> select sysdate from dual;
          
          SYSDATE
          -------------------
          11-27-2012 11:47:37
          Note in Forms, you can use two datatypes: DATE and DATETIME The both will query a date from the database, but the DATE datatype will remove the time element in the form, so a data change occurs. If you want to see the full date and time from the database column, use DATETIME, and set the Format Mask to something that will display everything.

          Edited by: Steve Cosner on Nov 27, 2012 11:42 AM
          • 2. Re: quering with sysdate function
            salute-Salem
            thanks