Reference Guide > TDV Support for SQL Functions > TDV-Supported Numeric Functions > ROUND (for date/time)
 
ROUND (for date/time)
Given two input arguments, this form of the ROUND function returns the value of the first input argument rounded to the value specified by the second input argument (format).
Syntax
ROUND (input_arg, format)
Remarks
• The input argument is the keyword DATE or TIME or TIMESTAMP plus a quoted string containing the date/time expression to truncate.
• If the format argument is not present:
— TIMESTAMP rounds up or down to a day, with a time of 00:00:00.
— DATE or the date portion of a TIMESTAMP remains unchanged.
— TIME or the time portion of a TIMESTAMP rounds down to the given hour or up to the next hour, with 00:00 minutes and seconds.
• The optional second argument, format, is a STRING. Its values are listed in the table below. This argument is not case-sensitive.
Format Argument
Output and Comments
CC
SCC
Beginning with January 1 of xx50, rounds up to the first day of the next century. Up to December 31 of xx49, rounds down to the beginning day of the current century. For example, 2050-01-01 rounds to 2101-01-01; 2049-12-31 rounds to 2001-01-01.
SYEAR, SYYYY
YEAR, YYYY, YYY, YY, Y
Year. Starting on July 1, rounds up to the next year.
IYYY, IYY, IY, I
Date of first day of the ISO year. An ISO year (ISO 8601 standard) starts on Monday of the week containing the first Thursday of January. It can start as early as 12/29 of the previous year, or as late as 01/04 of the current year.
Q
Date of the first day of the current quarter (up to the fifteenth of the second month of the quarter). Beginning on the sixteenth day of the second month of the quarter, rounds up to the first day of the next quarter.
MONTH, MON, MM, RM
Date of the first day of the current month (up to the fifteenth day). Beginning on the sixteenth day of the month, rounds up to the first day of the next month.
WW
Date of the same day of the week as the first day of the year.
IW
Because an ISO year always begins on a Monday: date of Monday of the current week if the first argument is Monday through Wednesday; date of Monday of the following week if the first argument is Thursday through Sunday.
W
Date of the same day of the week as the first day of the month.
DDD, DD, J
For 12:00:00 (noon) or later, rounds up to date of the following day. For 11:59:59 or before, or for a DATE, rounds down to current date.
DAY, DY, D
Starting day of the week; that is, date of the Sunday of the week that current date is in.
IDDD
ISO day of year, where day 1 of the year is Monday of the first ISO week. Range is 001-371.
ID
ISO day of the week, where Monday = 1 and Sunday = 7.
HH, HH12, HH24
For hour plus 30 minutes or later, rounds up to next hour.
MI
For minute plus 30 seconds or later, rounds up to next minute.
Examples
The table gives examples of ROUND with some of its format definitions and the results.
SELECT Statement
Result
ROUND (TIMESTAMP '1949-12-31 00:00:00', 'cc')
1901-01-01 00:00:00
ROUND (DATE '1950-01-01', 'cc')
2001-01-01
ROUND (timestamp '1983-07-01 15:59:31','Y')
1984-01-01 00:00:00
ROUND (date '1983-06-30', 'y')
1983-01-01
ROUND (timestamp '2015-03-06 15:59:31','i')
2014-12-29 00:00:00
ROUND (date '2015-03-06', 'i')
2014-12-29
ROUND (timestamp '1983-03-06 15:59:31','q')
1983-01-01 00:00:00
ROUND (date '1983-03-06', 'Q')
1983-01-01
ROUND (timestamp '1983-03-06 12:34:56', 'mm')
1983-03-01 00:00:00
ROUND (date '1983-03-06', 'mm')
1983-03-01
ROUND (timestamp '2015-06-08 12:34:56', 'ww')
2015-06-11 00:00:00
ROUND (date '2015-06-08', 'ww')
2015-06-11
ROUND (timestamp '2015-06-07 12:34:56', 'ww')
2015-06-04 00:00:00
ROUND (date '2015-06-107', 'ww')
2015-06-04
ROUND (timestamp '2015-06-10 12:34:56', 'ddd')
2015-06-10 00:00:00
ROUND (date '2015-06-10', 'ddd')
2015-06-10
ROUND (TIMESTAMP '2015-06-10 12:34:56', 'hh')
2015-06-10 12:00:00
ROUND (time '12:34:56', 'hh')
12:00:00
ROUND (TIMESTAMP '2015-06-10 12:34:56', 'mi')
2015-06-10 12:34:00
ROUND (time '12:34:56', 'mi')
12:34:00