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 up to the minimum date_part multiple that is greater than or equal to the input timestamp.
dateceiling ( 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 determines the next largest date_part value expressed in the timestamp, and zeros out all date_parts of finer granularity than date_part.
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 ceiling operation. For example, to establish a date ceiling based on 10 minute intervals, use MINUTE or MI for the date_part, and 10 as the multiple.
SAP extension.
dateceiling( 'MINUTE', to_timestamp('2010-05-04T12:00:01.123', 'YYYY-MM-DDTHH24:MI:SS.FF'))
returns '2010-05-04 12:01:00.000'