DATEROUND()

Scalar. Computes a new timestamp based on the provided timestamp, multiple and date_part arguments, with subordinate date_parts set to zero. The result is then rounded to the value of a date_part multiple that is nearest to the input timestamp.

Syntax

DATEROUND( date_part, expression [, multiple] )

Parameters
date_part	Keyword identifying the granularity desired, where the valid keywords are identical to the date parts supported for the existing function, datepart().
expression	A date-time expression that contains the value to be evaluated.
multiple	Contains a multiple of date_parts to be used in the operation. If supplied, this needs to be a non-zero positive integer value. If none is provided or is NULL, this is assumed to be 1.

Usage

This function rounds the datetime value to the nearest date_part or multiple of date_part, and zeroes out all date_parts of finer granularity than date_part or its multiple. For example, when rounding to the nearest hour, the minutes portion is determined, and if >= 30, then the hour portion is incremented by 1, and the minutes and other subordinate date parts are zeroes.

Date_part is a keyword, expression is any expression that evaluates or can be implicitly converted to a datetime (or timestamp) datatype, and multiple is an integer containing the multiples of date_parts to be used in performing the rounding operation. For example, to round to the nearest 10-minute increment, use MINUTE or MI for date_part, and 10 as the multiple.

Known errors:

The server generates an "invalid argument" error if the value of the required arguments evaluate to NULL.

The server generates an "invalid argument" error if the value of the multiple argument is not within a range valid for the specified datepart argument. As an example, the value of multiple must be less than 60 if date_part mi is specified.

Standards and compatibility

Sybase extension.

Example

DATEROUND( MINUTE,  "August 13, 2008 10:35.500AM") 
returns "August 13, 2008 10:36.000AM"