datefloor()

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 down to the maximum date_part multiple that is less than or equal to the input timestamp.

Syntax

datefloor ( 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 zeros out all datetime values with a granularity finer than that specified by date_part. Date_part is a keyword, and expression is any expression that evaluates or can be implicitly converted to a datetime (or timestamp) datatype. Multiple is an integer that contains the multiples of date_parts to be used in performing the floor operation. For example, to establish a date floor based on 10 minute intervals, 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, have the value of multiple be less than 60 if date_part mi is specified.

Standards and compatibility

SAP extension.

Example

datefloor( 'MINUTE', to_timestamp('2010-05-04T12:00:01.123', 'YYYY-MM-DDTHH24:MI:SS.FF'))
returns '2010-05-04 12:00:00.000'