ROUND(Date) - Teradata Database

ROUND(Date)

Purpose  

Returns date_value with the time portion of the day rounded to the unit specified by fmt.

Syntax  

where:

Syntax element …	Specifies …
TD_SYSFNLIB	the name of the database where the function is located.
date_value	a date or timestamp argument.
fmt	a character argument. If fmt is NULL, NULL is returned. If fmt is omitted, date_value is rounded to the nearest day. Sunday is considered the first day of the week. For ISO years, Monday is considered the first day of the week. If fmt is not valid, an error is returned.

ANSI Compliance

This is a Teradata extension to the ANSI SQL:2011 standard.

Invocation

ROUND(Date) is an embedded services system function. For information on activating and invoking embedded services functions, see “Embedded Services System Functions” on page 24.

Argument Types and Rules

Expressions passed to this function must have one of the following data types:

date_value = DATE, TIMESTAMP, or TIMESTAMP WITH TIME ZONE

fmt = VARCHAR CHARACTER SET LATIN

For the fmt argument, you can also pass values with data types that can be converted to VARCHAR using the implicit data type conversion rules that apply to UDFs. Implicit type conversion is not supported for the date_value argument.

Note: The UDF implicit type conversion rules are more restrictive than the implicit type conversion rules normally used by Teradata Database. If an argument cannot be converted to the required data type following the UDF implicit conversion rules, it must be explicitly cast.

For details, see “Compatible Types” in SQL External Routine Programming.

For date formats for ROUND(Date), see “TRUNC(Date)” on page 865.

Result Type

ROUND is a scalar function whose return data type is DATE.

Example  

The following query:

SELECT ROUND(CAST('2003/09/17' AS DATE), 'D') (FORMAT 'yyyy-mm-dd'); 

returns the result 2003-09-14. The date was rounded to the first day of that week.

Example  

The following query:

SELECT ROUND(CAST('2003/09/17' AS DATE), 'RM') (FORMAT 'yyyy-mm-dd'); 

returns the result 2003-10-01. Since the day is greater than or equal to 16, the date is rounded to the beginning of the next month.