Results 1 to 7 of 7

Thread: date

  1. #1
    Join Date
    Mar 2004
    Posts
    53

    Unanswered: date

    i,ve a table, tb_aduan. in this table there is a field where i save a date. i save the date as system date. i want to retrieve all the info for certain date. how can i do that.
    can someone help me.plzzzzzzzzzzzzzzzzzzzz

  2. #2
    Join Date
    Jan 2004
    Location
    Singapore
    Posts
    89

    Re: date

    Originally posted by fisya
    i,ve a table, tb_aduan. in this table there is a field where i save a date. i save the date as system date. i want to retrieve all the info for certain date. how can i do that.
    can someone help me.plzzzzzzzzzzzzzzzzzzzz
    suppose x is the column name of ur table tb_aduan, then ur query shoud be this way

    select * from tb_aduan
    where to_date(x) = to_date('01-JAN-03')

    suppose the certain date is 01-JAN-03
    Thanks and Regards,

    Praveen Pulikunnu

  3. #3
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Praveen,

    what would be the difference if we omit TO_DATE function and, therefore, simplify the query? (tb_aduan.x is of DATE type)
    Query runned as

    SELECT * FROM tb_aduan WHERE x = '01-jan-03';

    returns just the same in my database. Are there situations where this wouldn't be the case?

  4. #4
    Join Date
    Jan 2004
    Location
    Singapore
    Posts
    89
    Surely it can vary,

    suppose the person has inserted date field in the table
    as
    insert into tb_aduan(id,x)
    values(1001,sysdate)
    /
    This will insert the time stamp too.
    To avoid such kind of surprises, its better to use to_date function.
    Thanks and Regards,

    Praveen Pulikunnu

  5. #5
    Join Date
    Feb 2004
    Posts
    108
    Originally posted by praveenpr
    This will insert the time stamp too.
    To avoid such kind of surprises, its better to use to_date function.
    Still I would prefer Truncate over to_Date.

  6. #6
    Join Date
    Mar 2004
    Posts
    53

    enter date

    in my situation, user will enter the date they want and system will retrieve it and preview it at the secong page. when i do this i got error that says :
    mismatch........
    how to solve this

  7. #7
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: enter date

    Always use TO_DATE and an explicit format mask when comparing a date with a character string - and please use 4 digits for years!:

    SELECT * FROM tb_aduan
    WHERE x = TO_DATE('01-jan-2003','DD-MON-YYYY');

    (It is not safe to assume that the default format mask is what you expect.)

    Now, if the date column x could contain a time component and you want all x values for that date then you can either use TRUNC:

    SELECT * FROM tb_aduan
    WHERE TRUNC(x) = TO_DATE('01-jan-2003','DD-MON-YYYY');

    or you can check by range:

    SELECT * FROM tb_aduan
    WHERE x >= TO_DATE('01-jan-2003','DD-MON-YYYY')
    AND x < TO_DATE('01-jan-2003','DD-MON-YYYY')+1;

    If you use TRUNC then be aware that Oracle cannot use an index on (tb_aduan.x) to answer the query. However, you could create an index on (TRUNC(tb_aduan.x)).

Posting Permissions

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