The ROUND function rounds a date value to the nearest date as specified by a format mask. The TRUNC function truncates a date as specified by a format mask. ROUND and TRUNC for datetimes are analogous to ROUND and TRUNC for numbers. (See Section 9.3.1 in Chapter 9.) Their specifications are as follows:
FUNCTION ROUND ( date_in IN DATE [, format_mask VARCHAR2]) RETURN DATE FUNCTION TRUNC ( date_in IN DATE [, format_mask VARCHAR2]) RETURN DATEA common use for both functions is to set the time-of-day component of a DATE value to midnight (12:00:00 AM). By convention, midnight is the value you use when you care only about the date itself, not the time. The difference between the two functions lies in whether they disregard the time component entirely or use it to round the resulting DATE value up or down. Let's look at the different results from the two functions in the following example:
DECLARE date_in DATE:= TO_DATE('24-Feb-2002 05:16:00 PM','DD-MON-YYYY HH:MI:SS AM'); date_rounded DATE; date_truncated DATE;BEGIN date_rounded:= ROUND(date_in); date_truncated:= TRUNC(date_in); DBMS_OUTPUT.PUT_LINE(TO_CHAR(date_rounded, 'DD-MON-YYYY HH:MI:SS AM')); DBMS_OUTPUT.PUT_LINE(TO_CHAR(date_truncated,'DD-MON-YYYY HH:MI:SS AM'));END;The result is:
25-FEB-2002 12:00:00 AM24-FEB-2002 12:00:00 AMLook at the value for date_rounded. Do you see that the day has been advanced from 24-Feb to 25-Feb? That's because the time, 5:16 PM, was closer to midnight of the following day (the 25th) than it was to the 24th. In contrast, the value for date_truncated is still the 24th. The TRUNC function simply eliminated the time-of-day information. The ROUND function respected the time of day, eliminated it, but also advanced the date by one day. Had the time of day been 5:16 AM, the rounding would have been downward, and both functions would have returned the same result.
By default, the functions round or truncate to the date, as those operations tend to be by far the most common. However, using one of the format mask elements shown in Table 10-7, you can round to any other datetime element that you wish.
| Table 10-7. Format mask elements for ROUND and TRUNC | |
| Format mask | Rounds or truncates to |
| CC or SSC | Century |
| SYYY, YYYY, YEAR, SYEAR, YYY, YY, or Y | Year (rounds up to next year starting on July 1) |
| IYYY, IYY, IY, or I | Standard ISO year |
| Q | Quarter (rounds up on the sixteenth day of the second month of the quarter) |
| MONTH, MON, MM, or RM | Month (rounds up on the sixteenth day, which is not necessarily the same as the middle of the month) |
| WW | Same day of the week as the first day of the year |
| IW | Same day of the week as the first day of the ISO year |
| W | Same day of the week as the first day of the month |
| DDD, DD, or J | Day |
| DAY, DY, or D | Starting day of the week |
| HH, HH12, HH24 | Hour |
| MI | Minute |
The following example shows TRUNC being used to truncate a datetime value to the beginning of the year and the beginning of the month, respectively:
|
|
|
The output is:
01-JAN-2002 12:00:00 AM01-FEB-2002 12:00:00 AMAs you can see, TRUNC has enabled us to instantly jump back to the first of the year and the first of the month.
|
The following example shows ROUND being used in a few different ways. First, it is used to determine the century closest to 24-Feb-2002 and also to 24-Feb-1902. Then, it is used to round a datetime value to the nearest hour.
DECLARE date_in_1 DATE:= TO_DATE('24-Feb-2002','DD-MON-YYYY'); date_in_2 DATE:= TO_DATE('24-Feb-1902','DD-MON-YYYY'); date_in_3 DATE:= TO_DATE('24-Feb-2002 05:36:00 PM','DD-MON-YYYY HH:MI:SS AM'); round_1 DATE; round_2 DATE; round_3 DATE; BEGIN round_1:= ROUND(date_in_1,'CC'); round_2:= ROUND(date_in_2,'CC'); round_3:= ROUND(date_in_3,'HH'); DBMS_OUTPUT.PUT_LINE(TO_CHAR(round_1,'DD-MON-YYYY HH:MI:SS AM')); DBMS_OUTPUT.PUT_LINE(TO_CHAR(round_2,'DD-MON-YYYY HH:MI:SS AM')); DBMS_OUTPUT.PUT_LINE(TO_CHAR(round_3,'DD-MON-YYYY HH:MI:SS AM'));END;The output is:
01-JAN-2001 12:00:00 AM01-JAN-1901 12:00:00 AM24-FEB-2002 06:00:00 PMActually, rounding to the century doesn't return the century; it returns the date and time at which the century begins. And Oracle respects the scientific definition of century, not the popular definition, so, for example, the 21st century began in 2001, not 2000. The explanation of the third line of output in this example is straightforward: 5:36 PM was rounded to the nearest hour, which was 6:00 PM on the same date.
|
The TRUNC function is very useful in leveling the playing field with regard to the time component when comparing two DATE variables. Consider the following comparison:
IF request_date BETWEEN start_date AND end_dateTHEN...The date component of request_date and start_date might be the same, but if your application does not specify a time component for each of its dates, the comparison might fail. If, for example, the user enters a request_date and the screen does not include a time component, the time for request_date will be midnight (12:00 AM) of that day. If start_date was set from SYSDATE, however, its time component will reflect the time at which the assignment was made. Because 12:00 AM comes before any other time of the day, a comparison that looks to the naked eye like a match might well fail.
If you are not sure about the time components of your date fields and variables and want to make sure that your operations on dates disregard the time component, TRUNCate them:
|
|
|
TRUNC resets the time of day to midnight (12:00:00 AM). In this example, all datetime values are truncated to midnight, so the time will never cause this comparison to fail.
