Results 1 to 4 of 4

Thread: query time

  1. #1
    Join Date
    Aug 2009
    Posts
    2

    Smile Unanswered: query time

    Dear sir I have table aa1
    CREATE TABLE AA1
    (
    DATE_ DATE
    )
    The values in the tables are
    7/30/2009 10:00:00 AM
    7/30/2009 11:00:00 AM
    7/30/2009 10:00:00 PM
    7/30/2009 11:00:00 PM
    7/30/2009 12:00:00 PM
    I have written query to get time between

    SELECT TO_CHAR (date_, 'HH:MIS AM') FROM aa1 WHERE TO_CHAR (date_, 'HH:MIS AM') BETWEEN '11:00:00 AM' AND '12:00:00 PM'
    But the query doesn't return correct result.

    Can anybody help me?
    It will be very helpful to me.

    Thank you.

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    "The query doesn't return correct result" isn't very descriptive. What IS a correct result (from your point of view)? Anyway, when dealing with DATE datatype, I'd rather stick to it; something like this:
    Code:
    ...
    where to_date(to_char(date_, 'hh:mi PM'), 'hh:mi PM')
      between to_date('11:00 AM', 'hh:mi PM') 
          and to_date('12:00 PM', 'hh:mi PM')

  3. #3
    Join Date
    Aug 2009
    Posts
    2
    The values in the table are
    7/30/2009 10:00:00 AM
    7/30/2009 11:00:00 AM
    7/30/2009 10:00:00 PM
    7/30/2009 11:00:00 PM
    7/30/2009 12:00:00 PM
    The query is
    SELECT TO_CHAR (date_, 'HH:MI AM') FROM aa1 WHERE
    to_date(to_char(date_, 'hh:mi PM'), 'hh:mi PM')
    between to_date('11:00 AM', 'hh:mi PM')
    and to_date('12:00 PM', 'hh:mi PM')
    I think the result should be
    11:00:00 AM,10:00:00 PM,11:00:00 PM,12:00:00 PM
    But query's result is
    11:00:00 AM,12:00:00 PM

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    12:00 AM = midnight
    12:00 PM = noon

    Therefore, 11:00 AM - 12:00 PM takes only one hour (it is between 11:00 in the morning and noon) and there's no way that 10:00 PM (or 22:00), 11:00 PM (or 23:00) fit into that interval.

Posting Permissions

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