Team Fly 

Page 56

Month

Name of month as a nine-character name

select to_
char(sysdate,'Month')
from dual;

March followed by 4 spaces for all dates in March

WW

Week of year

select to_
char(sysdate,'WW')
from dual;

29 on July 15, 2004

W

Week of the month

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

3 on May 15, 2004

DDD

Day of the year

select to_
char(sysdate, 'DDD')
from dual;

359 on December 25
in non leap years

DD

Day of the month

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

09 on September 9
in any year

D

Day of the week
(1 through 7)

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

5 on January 29, 2004

TABLE 2-7. Common Formats of Date Type Data

Nested Functions

It is also common to nest functions within functions. Using the months_between example from Table 2-7, it would be possible to round the number of months between the two dates. The following statement and output illustrates this example.

SQL> select round(months_between('17-MAR-61','21-APR-62'))
  2  from dual;

ROUND(MONTHS_BETWEEN('17-MAR-61','21-APR-62'))
----------------------------------------------
                                           -13

The inner function is evaluated first, and then the outer function is evaluated second. This is true for all nested functions and as this example illustrates, different function types can be combined. Pay special notice to the parentheses for the outer and inner functions. For illustration purposes, this example nests only one function within another. However, it is possible to nest many functions within each other. Just be careful; the order of the functions is important, and the complexity of debugging nested functions increases with each additional nested function.

Team Fly 
0075