dateround()

Scalar. Computes a new date-time based on the provided date-time, 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 that identifies the granularity desired. Valid keywords are identified below.
expression Date-time expression containing the value to be evaluated.
multiple Contains a multiple of date_parts to be used in the operation, which if supplied must be a nonzero positive integer value. If none is provided or it is NULL, the value is assumed to be 1.

Valid Date Part Keywords and Multiples

Keyword Keyword meaning Multiples
yy or year Year Any positive integers
qq or quarter Quarter Any positive integers
mm or month Month Any positive integers
wk or week Week Any positive integers
dd or day Day Any positive integers.
hh or hour Hour 1, 2, 3, 4, 6, 8, 12 and 24
mi or minute Minute 1, 2, 3, 4, 5, 6, 10, 12, 15, 20, 30, and 60
ss or second Second 1, 2, 3, 4, 5, 6, 10, 12, 15, 20, 30, and 60
ms or millisecond Millisecond 1, 2, 4, 5, 8, 10, 20, 25, 40, 50, 100, 125, 200, 250, 500, and 1000

Usage

This function rounds the datetime value to the nearest date_part or multiple of date_part, and zeros 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 zeros.

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.

Example

dateround( 'MINUTE', to_timestamp('2010-05-04T12:00:01.123', 'YYYY-MM-DDTHH24:MI:SS.FF'))
returns '2010-05-04 12:00:00.000'
Parent topic: Date and Time Functions