Team Fly 

Page 55

add_months(d,n)

Adds n or subtracts -n months from date d.

select add_
months('21-APR-04', 2) from dual;

21-JUN-04

months_between(d1,d2)

Returns difference in months between date d1 and date d2.

select months_
between('17-MAR-61', '21-APR-62') from dual;

-13.129032

next_day(d,day)

Returns the date that corresponds with the day of the week after date d.

select
next_day('01-FEB-04', 'Saturday') from dual;

07-FEB-04

TABLE 2-6. Common Date Functions

Special Formats with the Date Data Type

Date formats are used to change the display format of a date. This is done using the to_char conversion function along with the date and format mask. Table 2-7 shows a sample of the commoner date formats and their output.

Format Mask

Returns

Example

Displays

Y or YY or YYY

Last one, two, or three digits of year

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

004 for all dates in 2004

YEAR

Year spelled out

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

TWO THOUSAND FOUR in 2004

Q

Quarter of year

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

3 for all dates in August

MM

Month

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

12 for all dates
in December

Team Fly 
0074