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.
.-,--'DD'----------. >>-ROUND_TIMESTAMP--(--expression--+------------------+--)----->< '-,--format-string-'
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.
| 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
|
Input value: 1897-12-04-12.22.22.000000
|
| YYYY SYYYY YEAR SYEAR YYY YY Y |
Year (Rounds up on July 1 to
January 1st of the next year ) |
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 to 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. ) |
Input value: 1897-12-04-12.22.22.000000
|
Input value: 1897-12-04-12.22.22.000000
|
| 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) | Input value: 2000-06-21-12.12.30.000000 Result: 2000-06-21-00.00.00.000000 ![]() |
Input value: 2000-06-22-12.12.30.000000 Result: 2000-06-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 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 |
![]() 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. ![]() |
|||
SET :RND_TMSTMP = ROUND_TIMESTAMP('2000-03-18-17.30.00', 'MONTH');Host variable RND_TMSTMP is set with the value 2000-04-01-00.00.00.000000.