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.
dateround ( date_part, expression [, multiple] )
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. |
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 |
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.
dateround( 'MINUTE', to_timestamp('2010-05-04T12:00:01.123', 'YYYY-MM-DDTHH24:MI:SS.FF')) returns '2010-05-04 12:00:00.000'