DATECEILING()

Scalar. Computes a new timestamp based on the provided timesamp, multiple and date_part arguments, with subordinate 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.

Syntax

DATECEILING( date_part, expression [, multiple] )

Parameters
date_part	Keyword that identifies the granularity desired. The valid keywords are identical to the date parts supported for the existing function, datepart().
expression	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 should be a non-zero positive integer value. If none is provided or it is NULL, this is assumed to be 1.

Usage

This function determines the next largest date_part value expressed in the timestamp, and zeroes 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.

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

Sybase extension.

Example

DATECEILING( MINUTE, "August 13, 2008 10:35.123AM")
returns "August 13, 2008 10:36.000AM"