Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    Join Date
    Dec 2008
    Posts
    99

    Unanswered: trunc to_date to_char

    Hello
    can you explain how I should use these funcs?
    I generally do

    trunc(date)='01-01-2009'
    to_date(date)='01-01-2009'
    to_char(date)='01-01-2009'

    in which situations should I use these

    Thanks

  2. #2
    Join Date
    Mar 2007
    Posts
    623
    Quote Originally Posted by sunsail
    trunc(date)='01-01-2009'
    to_date(date)='01-01-2009'
    to_char(date)='01-01-2009'

    in which situations should I use these
    For your own good, never use any of these constructions.

    I do not know, which "situations" you face; anyway, the best ways for checking, whether <date> column of DATE data type belongs into given (daily) interval (which the posted pieces of code seem to check), are these ones:
    Code:
    <date> >= to_date('01-01-2009', 'dd-mm-yyyy')
      and <date> < to_date('02-01-2009', 'dd-mm-yyyy')
    or
    Code:
    <date> between to_date('01-01-2009 00:00:00', 'dd-mm-yyyy hh24:mi:ss')
      and to_date('01-01-2009 23:59:59', 'dd-mm-yyyy hh24:mi:ss')
    Yes, you may also use (for condition on one day)
    Code:
    trunc(<date>) = to_date('01-01-2009', 'dd-mm-yyyy')
    , anyway it is not recommended as it cannot use index on <date> column (if available).

  3. #3
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    TRUNC will remove "time" component from a date value:
    Code:
    SQL> select sysdate, trunc(sysdate) from dual;
    
    SYSDATE             TRUNC(SYSDATE)
    ------------------- -------------------
    02.07.2009 13:57:33 02.07.2009 00:00:00
    TO_DATE of a DATE doesn't make sense - why would you convert date to a date?

    TO_CHAR of a date is used to format DATE value accordingly to your needs:
    Code:
    SQL> select to_char(sysdate, 'dd-mon-yyyy hh24:mi') from dual;
    
    TO_CHAR(SYSDATE,'
    -----------------
    02-jul-2009 13:59

  4. #4
    Join Date
    Dec 2008
    Posts
    99
    Normally one column in the table has timestamp(6) type,and If I query this table I do this

    select...
    where
    trunc(create_date)=trunc('17-04-2009'

    however this does not work,I know there is data in the table with this date entry.

    Thanks

  5. #5
    Join Date
    Dec 2008
    Posts
    99
    I have timestamp(6) type field ,here one example,

    crea_date=('17-APR-09 02.34.26.637000000 PM)

    I m querying data of "17 april" how can I do this?

    select ....
    where
    create_date='17-05-2009'

    I tried this
    to_date(create_date,'dd-mm-yyyy')='17-05-2009'

    Thanks

  6. #6
    Join Date
    Mar 2007
    Posts
    623
    When comparing two values, keep one basic rule: always compare two values with the same type. Otherwise, Oracle implicit conversion may come to place with sometimes surprising result. Preferably, compare DATE with DATE (or TIMESTAMP with TIMESTAMP in your case).

    Second rule: when using Oracle built-in functions, pass parameters having exactly the same type they support. They are described in SQL Reference book, available with other documentation e.g. online on http://tahiti.oracle.com/. Please, read description of these functions (TO_TIMESTAMP, TO_DATE, TO_CHAR, TRUNC) before using them.

    Did you read my post? Simply replace TO_DATE with TO_TIMESTAMP (as <date> column has TIMESTAMP data type) and use any of the first two you like more.

  7. #7
    Join Date
    Dec 2008
    Posts
    99
    I tried

    where
    cstep.creation_date=to_timestamp('17-APR-09 02.34.26.637000000 PM','DD-MON-YY HH12.MI.SS.SSSSS PM')

    I m getting ora-01855 error.

  8. #8
    Join Date
    Mar 2007
    Posts
    623
    Quote Originally Posted by flyboy
    Please, read description of these functions (TO_TIMESTAMP, TO_DATE, TO_CHAR, TRUNC) before using them.
    They use format masks which are also described in the SQL Reference book.

    'PM' is not valid format mask - AM/PM specification is represented by 'AM' mask.

    Ready, fire, aim (or trial and error) method is not the best way to develop.
    Maybe you shall study the documentation first.

  9. #9
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Right; there's a book about a Spanish guy named Manual. You should read it.

  10. #10
    Join Date
    Dec 2008
    Posts
    99
    Quote Originally Posted by flyboy
    They use format masks which are also described in the SQL Reference book.

    'PM' is not valid format mask - AM/PM specification is represented by 'AM' mask.

    Ready, fire, aim (or trial and error) method is not the best way to develop.
    Maybe you shall study the documentation first.
    here it says somwthinh wrong then?
    Oracle/PLSQL: To_Date Function

    I m trying this
    http://www.dbasupport.com/forums/arc...p/t-46511.html

  11. #11
    Join Date
    Mar 2007
    Posts
    623
    Thank you for the link about format masks. You might be interested in these ones:
    SS Second (0-59).
    SSSSS Seconds past midnight (0-86399).
    FF Fractional seconds. Use a value from 1 to 9 after FF to indicate the number of digits in the fractional seconds. For example, 'FF4'.
    Can you explain, why you used 'SS.SSSSS'? Instead of the fact it is duplicated, it is also inconsistent (as 02.34.26 PM = 52466 seconds after midnight).
    Did you not want to use FF format mask instead? Something like
    Code:
    SQL> select to_timestamp('17-APR-09 02.34.26.637000000 PM','DD-MON-YY HH12.MI.SS.FF9 PM') from dual;
    
    
    TO_TIMESTAMP('17-APR-0902.34.26.637000000PM','DD-MON-YYHH12.MI.SS.FF9PM')
    ---------------------------------------------------------------------------
    17-APR-09 02.34.26.637000000 PM
    
    1 row selected.
    
    SQL>

  12. #12
    Join Date
    Dec 2008
    Posts
    99
    Quote Originally Posted by flyboy
    Thank you for the link about format masks. You might be interested in these ones:
    Can you explain, why you used 'SS.SSSSS'? Instead of the fact it is duplicated, it is also inconsistent (as 02.34.26 PM = 52466 seconds after midnight).
    Did you not want to use FF format mask instead? Something like
    Code:
    SQL> select to_timestamp('17-APR-09 02.34.26.637000000 PM','DD-MON-YY HH12.MI.SS.FF9 PM') from dual;
    
    
    TO_TIMESTAMP('17-APR-0902.34.26.637000000PM','DD-MON-YYHH12.MI.SS.FF9PM')
    ---------------------------------------------------------------------------
    17-APR-09 02.34.26.637000000 PM
    
    1 row selected.
    
    SQL>

    Hello,
    I donot know why but I posted an email in this thread to epxlain situation ,and now I cannot see that post,did I mispost it?

    Anyway problem is that I was querying wrong column instead of one I was supposed to query.Now I can get results with trunc function in

    trunc(send_date)=trunc('01-022009')

    format as I was used to get.

    Sorry for taking your time and making busy.

    ps:I didnot create table structure therefore I donot know why "ss.sssss" is used.

    Best Regards

  13. #13
    Join Date
    Mar 2007
    Posts
    623
    Quote Originally Posted by sunsail
    I donot know why but I posted an email in this thread to epxlain situation ,and now I cannot see that post,did I mispost it?
    I have no idea, I received only this reply.
    Quote Originally Posted by sunsail
    Now I can get results with trunc function in

    trunc(send_date)=trunc('01-022009')

    format as I was used to get.
    Congratulations; anyway the code you posted 'does not work' for me:
    Code:
    SQL> select trunc('01-022009') from dual;
    select trunc('01-022009') from dual
                 *
    ERROR at line 1:
    ORA-01722: invalid number
    
    
    SQL>
    Quite expected result, as TRUNC function accepts only numeric or datetime type parameters, not strings. And conversion to both types fails for '01-022009', as it does not represent any number nor date.
    The only reasonable explanation is, that you post here some kind of pseudocode. Please do not do it: it is very misleading to all readers. In fact, the first thing anybody will do, is correcting this rubbish instead of finding solution(s) for your problem.
    Quote Originally Posted by sunsail
    ps:I didnot create table structure therefore I donot know why "ss.sssss" is used.
    As you could see my example, I did not create any table structure too.
    It is all about converting VARCHAR2 literal into TIMESTAMP. And knowing, what the string representation ('17-APR-0902.34.26.637000000PM') means.
    Anyway, using 'SS.SSSSS' was the reason of the ORA-01855 error.

    [edit: Added the remark about ORA-01855]
    Last edited by flyboy; 07-06-09 at 12:08.

  14. #14
    Join Date
    Dec 2008
    Posts
    99
    Hi Flyboy

    trunc(send_date)=trunc('01-022009') this is what I posted,I mistyped it.I m sorry I was trying to write quickly.But it is suprising you tried it as I wrote it.

    Try this
    trunc(send_date)=trunc('01-02-2009').It works in oracle11G.

    You could have assumed it easily mistyping.

    I got this error,my point is that I wouldnot put date data in this "ss.ssss" format,that's why I said I didnot create table structure.

    Best Regards


    Quote Originally Posted by flyboy
    I have no idea, I received only this reply.

    Congratulations; anyway the code you posted 'does not work' for me:
    Code:
    SQL> select trunc('01-022009') from dual;
    select trunc('01-022009') from dual
                 *
    ERROR at line 1:
    ORA-01722: invalid number
    
    
    SQL>
    Quite expected result, as TRUNC function accepts only numeric or datetime type parameters, not strings. And conversion to both types fails for '01-022009', as it does not represent any number nor date.
    The only reasonable explanation is, that you post here some kind of pseudocode. Please do not do it: it is very misleading to all readers. In fact, the first thing anybody will do, is correcting this rubbish instead of finding solution(s) for your problem.

    As you could see my example, I did not create any table structure too.
    It is all about converting VARCHAR2 literal into TIMESTAMP. And knowing, what the string representation ('17-APR-0902.34.26.637000000PM') means.
    Anyway, using 'SS.SSSSS' was the reason of the ORA-01855 error.

    [edit: Added the remark about ORA-01855]

  15. #15
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    trunc('01-02-2009') ONLY works because your NLS_DATE format is mm-dd-yyyy. At my site it is dd-mon-rr. Do not get into the habit of relying on the NLS settings, if it changes or the software is at another site it will always fail. Use

    to_date('01-02-2009','mm-dd-yyyy')

    Because the trunc is expecting a date, it implicitly converts the string to a date and then truncs it. A very bad habit to get into.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Posting Permissions

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