Results 1 to 8 of 8

Thread: Date/Time Stamp

  1. #1
    Join Date
    Aug 2004
    Posts
    19

    Unanswered: Date/Time Stamp

    Hi All,

    I want to know how to write the following query in Oracle SQL:

    select *
    from TABLE
    where UPDATE_TIME = '11/01/2004%'

    Oracle doesn't seem to like the way I'm phrasing that (I get an "Invalid Time" error). The time format in UPDATE_TIME is like this:

    "11/01/2004 1:31:29 AM"

    How can I just retrieve all records for a specified date (regardless of time)?

  2. #2
    Join Date
    Dec 2003
    Posts
    1,074
    select *
    from TABLE
    where UPDATE_TIME >= to_date('11/01/2004','mm/dd/yyyy') and < to_date('11/02/2004','mm/dd/yyyy')

    this will work, as the default time of 12am is inferred if left out. If you want to be more explicit:

    select *
    from TABLE
    where UPDATE_TIME between to_date('11/01/2004','mm/dd/yyyy') and to_date('11/01/2004 23:59:59','mm/dd/yyyy hr24:mi:ss')

    -Chuck

  3. #3
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    select * from TABLE
    where TRUNC(UPDATE_TIME) = TO_DATE( '11/01/2004','MM/DD/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.

  4. #4
    Join Date
    Aug 2004
    Posts
    19
    Thanks a lot guys! Got me out of a huge bind. I can sleep soundly tonight.


  5. #5
    Join Date
    Dec 2003
    Posts
    1,074
    Just a quick question, won't the TRUNC function cause the optimizer to ignore any indexes on this field, when they exist? That's why we avoid it.

    -cf

  6. #6
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    Hello chuck_forbes,

    I don't think TRUNC makes the optimizer completely ignore indexes, but in tests I've done, TRUNC was almays slower than direct date comparison as you proposed. It did use the same index, but didn't access it the same way : with TRUNC, it made a FAST FULL INDEX SCAN while without TRUNC, it made a RANGE SCAN which proved to be much faster.

    TRUNC is useful in some cases (in the select clause for example, or in a group by), but I personally avoid to put it on a table field in the where clause if I can avoid it.

    Anyway, I'd be happy to have other people's opinion on this issue.

    Regards,

    RBARAER

    PS : I think that in your first query you omitted UPDATE_TIME in your second condition, and that in this very condition, '12/01' would be better than '11/02' :
    Code:
    select *
    from TABLE
    where UPDATE_TIME >= to_date('11/01/2004','mm/dd/yyyy') and UPDATE_TIME < to_date('12/01/2004','mm/dd/yyyy')

  7. #7
    Join Date
    Apr 2004
    Posts
    246
    using 12/01 would give a month of data. the question was to get a day of data.

    In v10, the optimizer is getting smart enough to realize that people use trunc(date) rather than date between, so it rewrites the query for you (no always, but it can), and uses an index (v9, as you pointed out, uses the FFS, while v8 would just ignore the index)
    Give a man a fish, you feed him for a day. Club him over the head with a fish, he'll leave you alone.

  8. #8
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    Thanks shoblock for these precisions. Just as you thought, I'm using 9i (I forgot to tell it earlier, sorry).

    Concerning day or month, I'm sorry. I didn't have a look at the format...


    Too used to DD/MM/YYYY...

    Here is the correct version :

    Code:
    select *
    from TABLE
    where UPDATE_TIME >= to_date('11/01/2004','mm/dd/yyyy') and UPDATE_TIME < to_date('11/02/2004','mm/dd/yyyy')
    Thanks and regards,

    RBARAER

Posting Permissions

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