Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2003

    Unanswered: Inserting Current Date into a Query

    I'm creating custom report that queries an Oracle 8i table for records with a receving date of the current date. This report has to be "registered" in an application interface, so I can't really prompt for the date. I want to use something like:

    select * from rcv_ship where rcv_dt = SYSDATE

    I realize that this does not work. The table has the date stored in the standard format of "02-OCT-2003", any help?

  2. #2
    Join Date
    Sep 2003
    The Netherlands

    why wouldn't using SYSDATE not work ?
    If it is because of the different format, you can format it by using TO_CHAR, but only when the column of the database table storing the date is a varchar. If it is a date, then you don't have to format it, Oracle stores dates always in it's internal format and a call to sysdate is always in that format.
    Beware of timestamps. If needed, truncate the sysdate

    Hope this helps
    Edwin van Hattem
    OCP DBA / System analyst

  3. #3
    Join Date
    Oct 2003
    I thought the same thing however when I run my statement like this:

    select * from rcv_ship where rcv_dt='03-OCT-2003'

    ...I get the required results, however when I run it as:

    select * from rcv_ship where rcv_dt=SYSDATE

    ...I get 0 records. When you DESC that table the date is a true DATE field, so I'm confused. In the table the date is stored as:

    2003-10-02 00:00:00

    ...any ideas?

  4. #4
    Join Date
    Nov 2002
    Desk, slightly south of keyboard
    SYSDATE also returns the time portion, so your query is looking for an entry with the exact date time equivalent to SYSDATE.

    Try this instead...

    select * from rcv_ship where rcv_dt=TRUNC( SYSDATE )


  5. #5
    Join Date
    Oct 2003
    That was it...thanks that had me puzzled for the longest time...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts