ROUND (for dates and time) |
When a DATETIME expression is specified as an argument, the ROUND function returns a date and time value rounded to a specified date format. When you do not specify a format, the date and time value is rounded to the nearest day.
Return Value
DATETIME
Syntax
ROUND(datetime_exp, format)
Arguments
An expression that identifies a date and time number.
A text expression that specifies one of the format models shown in the following table. A format model indicates how the date and time number should be rounded.
Format Models for ROUND for Dates and Time
| Format Model | Description |
|---|---|
| CC
SCC |
One greater than the first two digits of a 4-digit year to indicate the next century. For example, 1900 becomes 2000. S indicates that BC dates are marked with a negative (-) prefix.
|
| D
DAY DY |
Starting day of the week (1 to 7). The day of the week that is number 1 is controlled by NLS_TERRITORY. |
| DD | Day of month (1 to 31). |
| DDD | Day of year (1 to 366). |
| HH
HH12 |
Hour of day (1 to 12). |
| HH24 | Hour of day (0 to 23). |
| IW | Same day of the week as the first day of the ISO year. |
| IYY
IY I |
Last 3, 2, or 1 digit(s) of ISO year. |
| IYYY | 4-digit year based on the ISO standard. |
| J | Julian day; that is, the number of days since January 1, 4712 BC. |
| MI | Minute (0 to 59). |
| MM | Two-digit numeric abbreviation of month (01 to 12, where January is 01); month rounds up on the sixteenth day. |
| MON | Abbreviated name of the month; month rounds up on the sixteenth day. |
| MONTH | Name of the month padded with blanks to 9 characters; month rounds up on the sixteenth day. |
| Q | Quarter of year (1, 2, 3, 4; JAN to MAR is Q1); quarter rounds up on the sixteenth day of the second month of the quarter. |
| RM | Roman numeral month (I to XII, where January is I); month rounds up on the sixteenth day.
|
| WW | Same day of the week as the first day of the year. |
| W | Same day of the week as the first day of the month. |
| YEAR
SYEAR |
Nearest year, spelled out (rounds up on July 1). S indicates that BC dates are marked with a negative (-) prefix.
|
| YYYY
SYYYY |
Nearest 4-digit year (rounds up on July 1). S indicates that BC dates are marked with a negative (-) prefix.
|
| YYY
YY Y |
Last 3, 2, or 1 digit(s) of nearest year (rounds up on July 1). |
Examples