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.
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.
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.
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 13:29.
You do not need a parachute to skydive. You only need a parachute to skydive twice.