1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

doubts reg trunc functions and oracle 10g

Discussion in 'SQL PL/SQL' started by laxman, Dec 9, 2009.

  1. laxman

    laxman Forum Expert

    Messages:
    144
    Likes Received:
    0
    Trophy Points:
    230
    Location:
    MUMBAI
    Dear sir,
    KIndly clear me two doubts i have mentioned.

    1) what is the use of trunc function while manipulating date values ,is this is working same as to_char or to_date functions.suppose if we use trunc(sysdate - 5) what will be the output.

    2) I am new to oracle 10 g,if possible can you send me some new features being added in oracle 10 g and how we can differentiate with oracle 9i.

    Thanks and regards
    Laxman
     
  2. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    @ Admin/Sadik : Please move this thread to Queries and Discussion.

    @ Laxman: Please post your queries in 'Queries and Discussion' section and not in 'Article' section .

    TRUNC function is entirely different from TO_CHAR and TO_DATE.

    TO_CHAR function is used to convert the Numeric and Date literal to Character format(String).

    TO_DATE function is used to convert the Character/string literal to Date format
    (String).

    While TRUNC function truncates the Date and Numeric literals based on the <Format> parameter.

    Better you understand the difference yourself

    1. TO_CHAR
    2. TO_DATE
    3. TRUNC(with Date)
    4. TRUNC(with Number)
     
  3. Sadik

    Sadik Community Moderator Forum Guru

    Messages:
    1,906
    Likes Received:
    252
    Trophy Points:
    1,455
    Thanks Raj... Laxman, my friend you are nearing 50 posts, about time you posted in correct categories. :)
     
  4. laxman

    laxman Forum Expert

    Messages:
    144
    Likes Received:
    0
    Trophy Points:
    230
    Location:
    MUMBAI
    Thank u very much sir for your valubale information.Now i got the clear idea about the trunc function.
    Actually i have been asked in an interview that what could be the output if we use trunc(sysdate - 1) ?
    and still i need your help for this issue.

    Thanks and regards
    Laxman
     
  5. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    You can try it yourself. It will be truncated to the Midnight.

    Code (SQL):

    SQL> SELECT sysdate dt FROM dual;

    DT
    ---------
    10-DEC-09

    SQL> SELECT sysdate-1 dt FROM dual;

    DT
    ---------
    09-DEC-09

    SQL> SELECT TRUNC(sysdate-1) dt FROM dual;

    DT
    ---------
    09-DEC-09

    SQL> ALTER SESSION SET nls_date_format='DD-MON-YY HH24:MI:SS';

    SESSION altered.

    SQL> SELECT TRUNC(sysdate-1) dt FROM dual;

    DT
    ------------------
    09-DEC-09 00:00:00

    SQL>
     
     
  6. laxman

    laxman Forum Expert

    Messages:
    144
    Likes Received:
    0
    Trophy Points:
    230
    Location:
    MUMBAI
    Re: doubts reg trunc functions

    Thank you Sir

    Regards
    Laxman