DATEFLOOR()

Scalar. Computes a new timestamp based on the provided timestamp, multiple and date_part arguments, with subordinate 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. The valid keywords are identical to the date parts supported for the existing function, datepart().
expression	A date-time expression that contains the value to be evaluated.
multiple	Contains a multiple of date_parts to be used in the operation, which if supplied must be a non-zero positive integer value. If none is provided or it is NULL, this is assumed to be 1.

Valid date part keywords and their valid abbreviation keywords are:

Year		 	yy
Quarter		qq
Month			mm
Week			wk
day 	 		dd
hour			hh
minute		mi
second		ss
millisecond		ms

Usage

This function zeroes 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

Sybase extension.

Example

DATEFLOOR( MINUTE, "August 13, 2008 10:35.123AM")
returns "August 13, 2008 10:35.000AM"