ROUND_TIMESTAMP

The ROUND_TIMESTAMP scalar function returns a timestamp that is rounded to the unit that is specified by the timestamp format string.

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

The schema is SYSIBM.

expression
An expression that returns a value of any 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 with an actual length that is not greater than 255 bytes. Start of changeA time zone in a string representation of a timestamp is ignored.End of change For the valid formats of string representations of dates and timestamps, see String representations of datetime values.
format-string
An expression that returns a built-in character string or graphic string data type, with a length that is not greater than 255 bytes. 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. format-string must be a valid template for a timestamp, and not include leading or trailing blanks.

Allowable values for format-string are listed in the following table.

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

Century

Rounds up to the start of the next century after the 50th year of the century (for example on 1951–01–01–00.00.00).

Not valid for a TIME argument.
Input Value:
1897-12-04-12.22.22.000000
Result:
1901-01-01-00.00.00.000000
Input Value:
1897-12-04-12.22.22.000000
Result:
1801-01-01-00.00.00.000000
SYYYY
YYYY
YEAR
SYEAR
YYY
YY
Y
 Year (Rounds up on July 1st)
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 1st)
Input Value:
1897-12-04-12.22.22.000000
Result:
1898-01-03-00.00.00.000000
Input Value:
1897-12-04-12.22.22.000000
Result:
1897-01-04-00.00.00.000000
Q Quarter (Rounds up on the sixteenth 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 sixteenth 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.15.00.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 3rd 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.15.00.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 3rd 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.15.00.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 3rd day of the week, with respect to the first day of the month)
Input Value:
2000-05-17-12.12.30.000000
Result:
2000-05-15-00.00.00.000000
Input Value:
2000-05-17-12.15.00.000000
Result:
2000-05-15-00.00.00.000000
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 third 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 changeIf expression does not have data type TIMESTAMP WITHOUT TIME ZONE, expression is cast as follows:End of change

Start of change
  • If expression is a TIMESTAMP WITH TIME ZONE value, expression is cast to TIMESTAMP WITHOUT TIME ZONE, with the same precision as expression.
  • Otherwise, expression is cast to TIMESTAMP(6) WITHOUT TIME ZONE.
End of change

Start of changeThe result of the function has the same data type as the data type to which expression is cast.End of change

The result can be null; if any argument is null, the result is the null value.

The result CCSID is the appropriate CCSID of the argument encoding scheme and the result subtype is the appropriate subtype of the CCSID.

Example 1: Set the host variable RND_TMSTMP with the input timestamp rounded to the nearest year value. 
   SET :RND_TMSTMP = ROUND_TIMESTAMP(TIMESTAMP_FORMAT('2000-08-14 17:30:00', 
                                              'YYYY-MM-DD HH24:MI:SS'), 'YEAR');
The value set is '2001-01-01-00.00.00.000000'.
Start of changeExample 2: Assume PRSTSZ is an SQL variable with the TIMESTAMP WITH TIME ZONE value '2008-04-15.20.00.000000-08:30'. The input value is first cast to TIMESTAMP WITHOUT TIME ZONE (as '2008-04-15.20.00.000000') for the ROUND_TIMESTAMP function.
SELECT ROUND_TIMESTAMP(PRSTSZ) 
	FROM PROJECT;
The ROUND_TIMESTAMP function returns a TIMESTAMP WITHOUT TIME ZONE value of '2008-04-16.00.00.000000'.End of change