#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2004
    Posts
    20
    Rep Power
    0

    Question How do u select a date in oracle???


    When i type what is below, i get the order_entrydate returned for that ID. This order_entrydate was generated by sysdate in my asp code to oracle.

    SQL> select order_entrydate from orders where order_id=1;

    ORDER_ENT
    ---------
    31-MAR-04


    However, when i do it the opposite way, there are no rows selected. I tried various formats for the date, but i cant seem to return anything. what went wrong??

    SQL> select order_id from orders where order_entrydate='31-Mar-04';

    no rows selected
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2004
    Posts
    64
    Rep Power
    11
    Hi,

    When you do a date-'14-3-2004' it tries to match the date along with the hour (which in this case is 00:00:00) whereas the date column in the table is in a different hour/minut/second -

    try

    alter session set nls_date_format='dd-mm-yyyy hh24:mi:ss'

    and repeat.
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2004
    Posts
    28
    Rep Power
    0

    trunc() function


    Or, and a left side function like this is not optionmal, but you could just make your where clause
    trunc(order_date) = '31-MAR-04' ;

    *or*

    use a between...

    order_date between (trunc(order_date))
    and (trunc(order_date + 1))



    You may need to use a hint in the select if there are a lot of records and you decide to go with the left side trunc. This will do some consintent gets and physical reads.
    Last edited by jimbag8; March 31st, 2004 at 01:42 PM.
  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2004
    Posts
    2
    Rep Power
    0
    Hi,

    Try this :

    select order_id from orders
    where order_entrydate=to_date('31-Mar-04');

IMN logo majestic logo threadwatch logo seochat tools logo