This discussion is archived
2 Replies Latest reply: Nov 29, 2012 10:41 AM by newbi_egy RSS

quering with sysdate function

newbi_egy Explorer
Currently Being Moderated
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 Expert
    Currently Being Moderated
    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
    newbi_egy Explorer
    Currently Being Moderated
    thanks

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points