Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2004
    Posts
    6

    Unanswered: to_date and to_char problem?

    I have a PowerBuilder Report with Oracle 8 as the database. In the Front end, the user enters the start date and the end date and depending on these values, the report fetches the data from Oracle Tables.
    In the database, these dates are represented as Date, Length 7. It is represented as : 16-Aug-2004 in the table when I query it.

    THis is a part of the query which populates the report.

    where
    sar_act_event.act_end_date = To_Date('08162004' , 'mmddyyyyhh') and sar_act_event.act_event_date = To_Date('09262004' , 'mmddyyyyhh')

    what happens now is something strange. The report shud fetch data between the date ranges. However, it wont until u change the query to :

    sar_act_event.act_end_date >= To_Date('08162004' , 'mmddyyyyhh') and sar_act_event.act_event_date = To_Date('09262004' , 'mmddyyyyhh')

    operand for the start date shud be >= and then it will fetch values.

    why will this not work for '=' ?

    Looks like the start date is taken as : 08162004000000 (mm/dd/yyyyhh24mi)
    what shud i do to change this?
    the end date works well with operand '='

    Also, I tried to manupulate and use to_char funtion. For this, the '=' works, but none of the other operands work...

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Keep in mind that in Oracle "DATE" fields ALWAYS include a TIME element!
    Typically when all you are concerned about is the "date" portion TRUNC is used.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Jul 2003
    Posts
    2,296
    you are confusing WHAT YOU SEE with WHAT IS STORED in the DB.
    This is a common misconception regarding dates.
    When you select a date field all oracle shows you is whatever the NLS_DATE_FORMAT is set to.
    PHP Code:
    topicadm@Topic_Devselect sysdate from dual;

    SYSDATE
    ---------
    17-AUG-04
    /* does this mean Oracle doesn't know what TIME it is? no, this is just what we SEE */

    topicadm@Topic_Devalter session set NLS_DATE_FORMAT 'Mon dd yyyy HH24:MI';

    Session altered.

    topicadm@Topic_Devselect sysdate from dual;

    SYSDATE
    -----------------
    Aug 17 2004 14:42
    /* does this mean it is stored differently than above? NO. It is only displayed differently */ 
    for starters, your format field does not equal your data
    PHP Code:
    '08162004'  !=  'mmddyyyyhh' 
    all you list is month, day, year (no hours)

    as anacedent states, in reality I think you want to compare characters:
    PHP Code:
    where 
    to_char
    (sar_act_event.act_end_date'MMDDYYYY') = '08162004' 
    the above would match every date entry with that DAY (regardless of time during that day) which is something to keep in mind. It is basically converting all entries on Aug 16, 2004. If you want to match to a date you need to specify exact date and time.

    It all depends on what you are trying to match:
    1. exact date and time
    2. any entries on that specific day
    3. any entries during that specific HOUR of day
    4. or whatever
    Last edited by The_Duck; 08-17-04 at 15:48.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  4. #4
    Join Date
    Aug 2004
    Posts
    6
    Thanks for the reply.
    I tried trunc first and it worked.

Posting Permissions

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