Previous
Previous
 
Next
Next


ROUND (datetime)

The ROUND (datetime) function returns date rounded to the unit specified by the format model fmt. The value returned is always of data type DATETIME, even if you specify a different datetime data type for date.

Return Value

DATETIME

Syntax

ROUND(datetime_exp, [format ])

Parameters

datetime-exp

A datetime expression that identifies a date and time number.

format

A text expression that specifies a format model shown in the following table. A format model indicates how the date and time number should be rounded.

If you omit this argument, then datetime-exp is rounded to the nearest day.

Table: Datetime Format Templates for the ROUND and TRUNC Date Functions lists the format models you can use with the ROUND and TRUNC date functions and the units to which they round and truncate dates. The default model, 'DD', returns the date rounded or truncated to the day with a time of midnight.

Datetime Format Templates for the ROUND and TRUNC Date Functions

Format Model Rounding or Truncating Unit
CC
SCC

One greater than the first two digits of a four-digit year

SYYYY
YYYY
YEAR
SYEAR
YYY
YY
Y

Year (rounds up on July 1)

IYYY
IY
IY
I

ISO Year

Q

Quarter (rounds up on the sixteenth day of the second month of the quarter)

MONTH
MON
MM
RM

Month (rounds up on the sixteenth day)

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
J

Day

DAY
DY
D

Starting day of the week

HH
HH12
HH24

Hour

MI

Minute


The starting day of the week used by the format models DAY, DY, and D is specified implicitly by the initialization parameter NLS_TERRITORY.

Examples

Rounding to the Nearest Year

When the value of the NLS_DATE_FORMAT option is DD-MON-YY, then this statement:

SHOW ROUND (TO_DATE('27-OCT-92'),'year')

returns this value:

01-JAN-93

Rounding to Different Formats

Assume that you have a variable named mydatetime with the following definition and value defined in your analytic workspace.

DEFINE MYDATETIME VARIABLE DATETIME
DATE_FORMAT = 'MON-RRRR-DD-HH24'
mydatetime = CURRENT_TIMESTAMP
SHOW mydatetime
= 'AUG-2006-07-13'

As the following SHOW statements illustrate, depending on what date format value you specify, the ROUND function returns different values for the mydatetime variable.

SHOW ROUND(mydatetime, 'RRRR')
01-JAN-07
 
SHOW ROUND(mydatetime, 'MON')
01-AUG-06
 
SHOW ROUND(mydatetime, 'DD')
08-AUG-06
 
SHOW ROUND(mydatetime)
= 'AUG-2006-08-00'