ROUND_TIMESTAMP

The ROUND_TIMESTAMP function returns a timestamp that is the expression rounded to the unit specified by the format-string. If format-string is not specified, expression is rounded to the nearest day, as if 'DD' was specified for format-string.

Read syntax diagramSkip visual syntax diagram
                                   .-,--'DD'----------.      
>>-ROUND_TIMESTAMP--(--expression--+------------------+--)-----><
                                   '-,--format-string-'      

expression
An expression that returns a value of one of the following built-in data types: a timestamp, a character-string, or a graphic-string.
If expression is a character or graphic string, it must not be a CLOB or DBCLOB and its value must be a valid string representation of a timestamp. For the valid formats of string representations of timestamps, see String representations of datetime values.
format-string
An expression that returns a built-in character string data type or graphic string data type. format-string contains a template of how the timestamp represented by expression should be rounded. For example, if format-string is 'DD', the timestamp that is represented by expression is rounded to the nearest day. Leading and trailing blanks are removed from the string, and the resulting substring must be a valid template for a timestamp. The resulting value is then folded to uppercase, so the characters in the value may be in any case. Allowable values for format-string are listed in Table 1.

The result of the function is a TIMESTAMP. If either argument can be null, the result can be null; if either argument is null, the result is the null value.

Table 1. ROUND_TIMESTAMP and TRUNC_TIMESTAMP format models
Format model Rounding or truncating unit ROUND_TIMESTAMP example TRUNC_TIMESTAMP example
CC

SCC

One greater than the first two digits of a four digit year. (Rounds up on the 50th year of the century) Input value: 1897-12-04-12.22.22.000000

Start of changeResult: 1901-01-01-00.00.00.000000End of change

Input value: 1897-12-04-12.22.22.000000

Start of changeResult: 1801-01-01-00.00.00.000000End of change

YYYY

SYYYY

YEAR

SYEAR

YYY

YY

Y

Year (Rounds up on July 1 Start of changeto January 1st of the next yearEnd of change) Input value: 1897-12-04-12.22.22.000000

Result: 1898-01-01-00.00.00.000000

Input value: 1897-12-04-12.22.22.000000

Result: 1897-01-01-00.00.00.000000

IYYY

IYY

IY

I

ISO year (Rounds up on July 1 Start of changeto the first day of the next ISO year. The first day of the ISO year is defined as the Monday of the first ISO week.End of change) Input value: 1897-12-04-12.22.22.000000

Start of changeResult: 1898-01-03-00.00.00.000000End of change

Input value: 1897-12-04-12.22.22.000000

Start of changeResult: 1897-01-04-00.00.00.000000End of change

Q Quarter (Rounds up on the 16th day of the second month of the quarter) Input value: 1999-06-04-12.12.30.000000

Result: 1999-07-01-00.00.00.000000

Input value: 1999-06-04-12.12.30.000000

Result: 1999-04-01-00.00.00.000000

MONTH

MON

MM

RM

Month (Rounds up on the 16th day of the month) Input value: 1999-06-18-12.12.30.000000

Result: 1999-07-01-00.00.00.000000

Input value: 1999-06-18-12.12.30.000000

Result: 1999-06-01-00.00.00.000000

WW Same day of the week as the first day of the year (Rounds up on the 12th hour of the 4th day of the week, with respect to the first day of the year) Input value: 2000-05-05-12.12.30.000000

Result: 2000-05-06-00.00.00.000000

Input value: 2000-05-05-12.12.30.000000

Result: 2000-04-29-00.00.00.000000

IW Same day of the week as the first day of the ISO year (Rounds up on the 12th hour of the 4th day of the week, with respect to the first day of the ISO year) Input value: 2000-05-05-12.12.30.000000

Result: 2000-05-08-00.00.00.000000

Input value: 2000-05-05-12.12.30.000000

Result: 2000-05-01-00.00.00.000000

W Same day of the week as the first day of the month (Rounds up on the 12th hour of the 4th day of the week, with respect to the first day of the month) Start of changeInput value: 2000-06-21-12.12.30.000000

Result: 2000-06-21-00.00.00.000000

End of change
Start of changeInput value: 2000-06-22-12.12.30.000000

Result: 2000-06-15-00.00.00.000000

End of change
DDD

DD

J

Day (Rounds up on the 12th hour of the day) Input value: 2000-05-17-12.59.59.000000

Result: 2000-05-18-00.00.00.000000

Input value: 2000-05-17-12.59.59.000000

Result: 2000-05-17-00.00.00.000000

DAY

DY

D

Starting day of the week (Rounds up with respect to the 12th hour of the 4th day of the week. The first day of the week is always Sunday) Input value: 2000-05-17-12.59.59.000000

Result: 2000-05-21-00.00.00.000000

Input value: 2000-05-17-12.59.59.000000

Result: 2000-05-14-00.00.00.000000

HH

HH12

HH24

Hour (Rounds up at 30 minutes) Input value: 2000-05-17-23.59.59.000000

Result: 2000-05-18-00.00.00.000000

Input value: 2000-05-17-23.59.59.000000

Result: 2000-05-17-23.00.00.000000

MI Minute (Rounds up at 30 seconds) Input value: 2000-05-17-23.58.45.000000

Result: 2000-05-17-23.59.00.000000

Input value: 2000-05-17-23.58.45.000000

Result: 2000-05-17-23.58.00.000000

SS Second (Rounds up at 500000 microseconds) Input value: 2000-05-17-23.58.45.500000

Result: 2000-05-17-23.58.46.000000

Input value: 2000-05-17-23.58.45.500000

Result: 2000-05-17-23.58.45.000000

Start of change
Note:

The ISO year starts on the first day of the first ISO week of the year. This can be up to three days before January 1st or three days after January 1st. See WEEK_ISO for details.

End of change

Example