Results 1 to 8 of 8
  1. #1
    Join Date
    Mar 2004
    Posts
    205

    Unanswered: Trunc function ?

    Hi,

    Trunc(sysdate,'MM') returns the month in word format, but I need in number format i.e as '08'

    Please advice.

    SQL> select trunc(sysdate,'MM') from dual;

    TRUNC(SYS
    ---------
    01-AUG-04

    Thanks,
    Sam

  2. #2
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    That will do what you want :

    select to_char(sysdate, 'MM') from dual;

  3. #3
    Join Date
    Mar 2004
    Posts
    205
    But I want this to be returned as a date datatype with the 'MM' format.

  4. #4
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    You might want to:

    SQL> alter session set NLS_DATE_FORMAT = 'MM';

    Then you can do:

    SQL> select to_date(sysdate,'mm') from dual;

  5. #5
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    Ok. I think it might be useful to you to know how Oracle manages dates. The date format in Oracle is actually a 7 bytes array, each byte interpreted as an integer between 0 and 255, with :

    At index 0 : the century + 100 (120 for 2004)
    At index 1 : the year within the century + 100 (104 for 2004)
    At index 2 : the month
    At index 3 : the day
    At index 4 : the hours + 1 (16 for 15:30:00)
    At index 5 : the minutes + 1 (31 for 15:30:00)
    At index 6 : the seconds + 1 (01 for 15:30:00)

    Now, when you make Oracle display a date, it is always as a string, but whatever you want Oracle to display, the date is managed internally as shown above. Consequently, you can't return a date with "08" for august as a date format, but you can return a date string representation with "08", or return a date format, and then convert it to a string representation to display it. Note that in SQLPlus, you will always manage dates through one of their string representations. In JDBC or OCCI programs, however, you can work with the Oracle internal date format.

  6. #6
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    I somewhat agree with JMartinez, but it makes you alter your session, and I think the other date fields (other than month) will be filled with the other sysdate fields anyway : only what is displayed changes.

    Please someone tell me if I'm wrong.

  7. #7
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721

    Thumbs up

    Great explanation RBARAER , and you are right, when you alter your session, you are changing any apperance on your session (being this SQL*Plus or iSQL*Plus) of the parameter you changed! .. not internally, but rather graphically as you said.

  8. #8
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    The internal structure of the date NEVER changes. The only thing you can modify is how the date is displayed. For example if you want the number that corresponds to the current month then you would do.

    select to_number(to_char(sysdate,'MM')) from dual;
    Last edited by beilstwh; 08-12-04 at 14:29.
    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
  •