Database Reference
In-Depth Information
Table 9.1
Some of the ROUND and TRUNC Function Date Formatting
Format Characters
Rounding and Truncating
CC
The first year in a century.
YYYY, YEAR, YY
The nearest year, rounds up on July 1st.
Q
The nearest quarter, rounds up on the 16th of month two.
MONTH, MON, MM
The nearest month, rounds up on the 16th.
WW
The same day of the week as the first day of the year.
W
The same day of the week as the first day of the month.
DDD, DD
The day.
DAY, D
The first day of the week.
HH, HH12, HH24
The hour (HH24 is a 24-hour clock).
MI
The minute.
ROUND(date [, format]) and TRUNC(date [, format])
. These two
functions round up or truncate dates according to the format specifi-
cation. See Table 9.1 with date formatting rules for the ROUND and
TRUNC functions.
Some examples of date ROUND and TRUNC functions are as
follows. Let's say that our current date is 26-AUG-02.
ROUND(SYSDATE,'YEAR') = 01-JAN-03
TRUNC(SYSDATE,'YEAR') = 01-JAN-02
ROUND(SYSDATE,'MONTH') = 01-SEP-02
TRUNC(SYSDATE,'MONTH') = 01-AUG-02
ROUND(SYSDATE,'WW') = 27-AUG-02
TRUNC(SYSDATE,'WW') = 20-AUG-02
EXTRACT (format, date)
. The EXTRACT date function is proba-
bly one of the most useful and largely unknown date functions. For-
mat settings are simple, specific, and can be YEAR, MONTH, DAY,
HOUR, MINUTE, SECOND, or various TIMEZONE options.
