Preface
Function reference
- Function components
Constants
Operators
Dates
Functions
- Function overview
  - Aggregate functions
    - Filter conditions
  - Conversion functions
  - Data cleansing functions
  - Date functions
  - Encoding functions
  - Financial functions
  - Horizontal expansion functions
  - Numeric functions
  - Scientific functions
  - Special functions
  - String functions
  - Test functions
  - Window functions
    - Aggregate functions as window functions
    - Nested aggregate functions as window functions
- Function quick reference
- Functions in advanced mode
- %OPR_CONCAT%
- %OPR_CONCATDELIM%
- %OPR_IIF%
- %OPR_SUM%
- ABORT
- ABS
- ADD_TO_DATE
- AES_DECRYPT
- AES_ENCRYPT
- AES_GCM_DECRYPT
- AES_GCM_ENCRYPT
- ASCII
- AVG
- CEIL
- CHOOSE
- CHR
- CHRCODE
- COMPRESS
- CONCAT
- CONVERT_BASE
- COS
- COSH
- COUNT
- CRC32
- CUME
- DATE_COMPARE
- DATE_DIFF
- DEC_BASE64
- DECODE
- DECOMPRESS
- ENC_BASE64
- ERROR
- EXP
- FIRST
- FLOOR
- FV
- GET_DATE_PART
- GREATEST
- IIF
- IN
- INDEXOF
- INITCAP
- INSTR
- IS_DATE
- IS_NUMBER
- IS_SPACES
- ISNULL
- LAG
- LAST
- LAST_DAY
- LEAD
- LEAST
- LENGTH
- LN
- LOG
- LOWER
- LPAD
- LTRIM
- MAKE_DATE_TIME
- MAX (Dates)
- MAX (Numbers)
- MAX (String)
- MD5
- MEDIAN
- METAPHONE
- MIN (Dates)
- MIN (Numbers)
- MIN (String)
- MOD
- MOVINGAVG
- MOVINGSUM
- NPER
- PERCENTILE
- PMT
- POWER
- PV
- RAND
- RATE
- REG_EXTRACT
- REG_MATCH
- REG_REPLACE
- REPLACECHR
- REPLACESTR
- REVERSE
- ROUND (Dates)
- ROUND (Numbers)
- RPAD
- RTRIM
- SET_DATE_PART
- SETCOUNTVARIABLE
- SETMAXVARIABLE
- SETMINVARIABLE
- SETVARIABLE
- SHA256
- SIGN
- SIN
- SINH
- SOUNDEX
- SQRT
- STDDEV
- SUBSTR
- SUM
- SYSTIMESTAMP
- TAN
- TANH
- TO_BIGINT
- TO_CHAR (Dates)
- TO_CHAR (Numbers)
- TO_DATE
- TO_DECIMAL
- TO_FLOAT
- TO_INTEGER
- TRUNC (Dates)
- TRUNC (Numbers)
- UPPER
- VARIANCE
System variables
Datatype reference

Function Reference

Back Next

ROUND (Dates)

Rounds one part of a date. You can also use ROUND to round numbers.

This functions can round the following parts of a date:

Year.
Rounds the year portion of a date based on the month. If the month is between January and June, the function returns January 1 of the input year, and sets the time to 00:00:00. If the month is between July and December, the function returns January 1 of the next year with the time set to 00:00:00. For example, the expression

ROUND(06/30/1998 2:30:55, 'YY')

returns 01/01/1998 00:00:00, and

ROUND(07/1/1998 3:10:15, 'YY'

)

returns 1/1/1998 00:00:00.

Month.
Rounds the month portion of a date based on the day of the month. If the day of the month is between 1 and 15, it rounds the date to the first day of the input month with the time set to 00:00:00. If the day of the month is between 16 and the last day of the month, it rounds to the first day of the next month with the time set to 00:00:00. For example, the expression

ROUND(4/15/1998 12:15:00, 'MM')

returns 4/1/1998 00:00:00, and

ROUND(4/16/1998 8:24:19, 'MM')

returns 5/1/1998 00:00:00.

Day.
Rounds the day portion of the date based on the time. If the time is between 00:00:00 (12AM) and 11:59:59AM, the function returns the current date with the time set to 00:00:00 (12AM). If the time is 12:00:00 (12PM) or later, the function rounds the date to the next day with the time set to 00:00:00 (12AM). For example, the expression

ROUND(06/13/1998 2:30:45, 'DD')

returns 06/13/1998 00:00:00, and

ROUND(06/13/1998 22:30:45, 'DD')

returns 06/14/1998 00:00:00.

Hour.
Rounds the hour portion of the date based on the minutes in the hour. If the minute portion of the time is between 0 and 29, the function returns the current hour with the minutes and seconds set to 0. If the minute portion is 30 or greater, the function rounds to the next hour and sets the minutes and seconds to 0. For example, the expression

ROUND(04/01/1998 11:29:35, 'HH')

returns 04/01/1998 11:00:00, and

ROUND(04/01/1998 13:39:00, 'HH')

returns 04/01/1998 14:00:00.

Minute.
Rounds the minute portion of the date based on the seconds. If time has 0 to 29 seconds, the function returns the current minutes and sets the seconds to 0. If the time has 30 to 59 seconds, the function rounds to the next minute and sets the seconds to 0. For example, the expression

ROUND(05/22/1998 10:15:29, 'MI')

returns 05/22/1998 10:15:00, and

ROUND(05/22/1998 10:15:30, 'MI')

returns 05/22/1998 10:16:00.

Syntax

ROUND( date [,format] )

Argument	Required/ Optional	Description
date	Required	Date/Time datatype. You can nest TO_DATE to convert strings to dates before rounding.
format	Optional	Enter a valid format string. This is the portion of the date that you want to round. You can round only one portion of the date. If you omit the format string, the function rounds the date to the nearest day.

Return Value

Date with the specified part rounded. ROUND returns a date in the same format as the source date. You can link the results of this function to any column with a Date/Time datatype.

NULL if you pass a null value to the function.

Example

The following expressions round the year portion of dates in the DATE_SHIPPED column:

ROUND( DATE_SHIPPED, 'Y' )
ROUND( DATE_SHIPPED, 'YY' )
ROUND( DATE_SHIPPED, 'YYY' )
ROUND( DATE_SHIPPED, 'YYYY' )

DATE_SHIPPED	RETURN VALUE
Jan 15 1998 2:10:30AM	Jan 1 1998 12:00:00AM
Apr 19 1998 1:31:20PM	Jan 1 1998 12:00:00AM
Dec 20 1998 3:29:55PM	Jan 1 1999 12:00:00AM
NULL	NULL

The following expressions round the month portion of each date in the DATE_SHIPPED column:

ROUND( DATE_SHIPPED, 'MM' )
ROUND( DATE_SHIPPED, 'MON' )
ROUND( DATE_SHIPPED, 'MONTH' )

DATE_SHIPPED	RETURN VALUE
Jan 15 1998 2:10:30AM	Jan 1 1998 12:00:00AM
Apr 19 1998 1:31:20PM	May 1 1998 12:00:00AM
Dec 20 1998 3:29:55PM	Jan 1 1999 12:00:00AM
NULL	NULL

The following expressions round the day portion of each date in the DATE_SHIPPED column:

ROUND( DATE_SHIPPED, 'D' )
ROUND( DATE_SHIPPED, 'DD' )
ROUND( DATE_SHIPPED, 'DDD' )
ROUND( DATE_SHIPPED, 'DY' )
ROUND( DATE_SHIPPED, 'DAY' )

DATE_SHIPPED	RETURN VALUE
Jan 15 1998 2:10:30AM	Jan 15 1998 12:00:00AM
Apr 19 1998 1:31:20PM	Apr 20 1998 12:00:00AM
Dec 20 1998 3:29:55PM	Dec 21 1998 12:00:00AM
Dec 31 1998 11:59:59PM	Jan 1 1999 12:00:00AM
NULL	NULL

The following expressions round the hour portion of each date in the DATE_SHIPPED column:

ROUND( DATE_SHIPPED, 'HH' )
ROUND( DATE_SHIPPED, 'HH12' )
ROUND( DATE_SHIPPED, 'HH24' )

DATE_SHIPPED	RETURN VALUE
Jan 15 1998 2:10:31AM	Jan 15 1998 2:00:00AM
Apr 19 1998 1:31:20PM	Apr 19 1998 2:00:00PM
Dec 20 1998 3:29:55PM	Dec 20 1998 3:00:00PM
Dec 31 1998 11:59:59PM	Jan 1 1999 12:00:00AM
NULL	NULL

The following expression rounds the minute portion of each date in the DATE_SHIPPED column:

ROUND( DATE_SHIPPED, 'MI' )

DATE_SHIPPED	RETURN VALUE
Jan 15 1998 2:10:30AM	Jan 15 1998 2:11:00AM
Apr 19 1998 1:31:20PM	Apr 19 1998 1:31:00PM
Dec 20 1998 3:29:55PM	Dec 20 1998 3:30:00PM
Dec 31 1998 11:59:59PM	Jan 1 1999 12:00:00AM
NULL	NULL