Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2011
    Posts
    3

    Unanswered: to_date and to_char return different rows

    Hi Guys,

    Having issues with an oracle query that I am running.

    if I use to_char, I get expected results (approx 1200 rows returned), however, if I use to_date, I always get no rows returned???

    SQLs are:-
    select * from message_info where to_char(date_time_in,'DD/MM/YYYY') = '27/04/2011'

    select * from message_info where date_time_in = to_date('27/04/2011', 'DD/MM/YYYY')

    What am I doing wrong here ?
    date_time_in has a data type of: TIMESTAMP(6)

    We are running: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    DATE_TIME_IN contains "time" portion of data

    select * from message_info where trunc(date_time_in) = to_date('27/04/2011', 'DD/MM/YYYY')
    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
    Apr 2011
    Posts
    3
    Man that works perfectly, you rock!

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    do you understand what was wrong & why my solution works as desired?
    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.

  5. #5
    Join Date
    Apr 2011
    Posts
    3

    Talking

    Yes I understand now,

    I had assumed that by specifying 'DD/MM/YYYY' in the to_date clause, oracle would compare date_time_in as DD/MM/YYYY instead of DD/MM/YYYY HH24:MIS

    That is, my original comparison was:-

    date_time_in = to_date('27/04/2011', 'DD/MM/YYYY')
    27/04/2011 10:30:00 = 27/04/2011 -- which obviously fail the comparison and thus returned no rows.

    Now with the trunc function:-

    trunc(date_time_in) = to_date('27/04/2011', 'DD/MM/YYYY')
    27/04/2011 = 27/04/2011 -- which obviously matches.


    Again, thanks for your answer, it was exactly what I was after.

    Is there a way that I can mark this thread as answered by yourself ?
    If not, I am happy for the mods to close this thread.

Posting Permissions

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