Results 1 to 2 of 2

Thread: trunc(date)

  1. #1
    Join Date
    Oct 2003
    Location
    Brazil
    Posts
    30

    Angry Unanswered: trunc(date)

    fellas

    to shut this up once for all,

    using trunc(any_date) won't get the lines where the date has hour information too ?

    I've just queried some rows here using
    ...
    WHERE
    ...
    AND date_column = to_date(any_date, any_format)
    and it returned some rows with the column showing hour information and some not.

    As I'm always listening to everybody say that to get date with hours you must use the "trunc" function on that date...
    This seems strange.


    (P.S.: and using trunc in an indexed column disables this index ?)

    Thanks.

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    You need to think about what an Oracle DATE value is. It always contains a date (year, month, day) and it always contains a time (hour, minute, second). It is a convention that if we want "just" the date then we set the hours, minutes and seconds to 0 - which is what the TRUNC function does:
    Code:
    SQL> select to_char(sysdate,'YYYY,MM,DD,HH24,MI,SS') from dual;
    
    TO_CHAR(SYSDATE,'YY
    -------------------
    2005,01,14,16,27,24
    
    SQL> select to_char(trunc(sysdate),'YYYY,MM,DD,HH24,MI,SS') from dual;
    
    TO_CHAR(TRUNC(SYSDA
    -------------------
    2005,01,14,00,00,00
    So when you want to find all rows for a given date (e.g. today) then you have to consider whether we are stroring any time values (other than zeroes) in the date column. If we are not (e.g. if we always use TRUNC in the inserts to the table) then you can take the short-cut:
    Code:
    WHERE datecol = TRUNC(SYSDATE)
    But if there are non-zero time values in the column, then "today" means "any time today, from 00:00:00 to 23:59:59". There are various ways to perform that check:
    Code:
    WHERE TRUNC(datecol) = TRUNC(SYSDATE)
    WHERE datecol BETWEEN TRUNC(SYSDATE) AND TRUNC(SYSDATE)+1-1/24/60/60
    WHERE datecol >= TRUNC(SYSDATE) AND datecol < TRUNC(SYSDATE)+1
    You can take your pick! The first cannot use an index on datecol, but it can use an index on TRUNC(datecol).

Posting Permissions

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