Calculates a new date, time, or datetime value by reducing the provided value down to the nearest lower value of the specified multiple with the specified granularity.
Parameter |
Description |
|---|---|
date part |
The date part to be added to the date. |
datetime-expression |
The date, time, or date-time expression containing the value you are evaluating. |
multiple-expression |
(Optional). A nonzero positive integer value expression specifying how many multiples of the units specified by date-part to use within the calculation. For example, you can use multiple-expression to specify that you want to regularize your data to 200-microsecond intervals or 10-minute intervals If multiple-expression evaluates to zero, evaluates to a negative number, is an explicit NULL constant, or is not a valid value for the specified date-part, SAP Sybase IQ generates an error. If multiple-expression evaluates to a NULL, then the function result is NULL. |
SELECT DATEFLOOR( MINUTE, 'August 13, 2009 10:35:22.123AM') FROM iq_dummy
SELECT DATEFLOOR( US, 'August 13, 2009, 10:32:35.456789AM', 200 ) FROM iq_dummy
SELECT DATEFLOOR( US, 'August 13, 2009, 10:32:35.456789AM', 200000 ) FROM iq_dummy
SELECT DATEFLOOR( US, 'August 13, 2009, 10:32:35.456789AM') FROM iq_dummy
This function calculates a new date, time, or datetime value by reducing the provided value down to the nearest lower value with the specified granularity. If you include the optional multiple-expression parameter, then the function reduces the date and time down to the nearest specified multiple of the specified granularity.
The data type of the calculated date and time matches the data type of the multiple-expression parameter.
DayofYear
WeekDay
CalYearofWeek
CalWeekofYear
CalDayofWeek
Multiples of microsecond start from the current second
Multiples of millisecond start from the current second
Multiples of second start from the current minute
Multiples of minute start from the current hour
Multiples of hour start from the current day
For example, if you specify a multiple of two minutes, SAP Sybase IQ applies two minute intervals starting at the current hour.
For the microsecond, millisecond, second, minute, and hour date parts, specify a multiple-expression value that divides evenly into the range of the specified date part:
For hours, the valid multiple-expression values are: 1, 2, 3, 4, 6, 8, 12, 24
For seconds and minutes, the valid multiple-expression values are: 1, 2, 3, 4, 5, 6, 10, 12, 15, 20, 30, 60
For milliseconds, the valid multiple-expression values are: 1, 2, 4, 5, 8, 10, 20, 25, 40, 50, 100, 125, 200, 250, 500, 1000
For microseconds, the valid multiple-expression values are:
1 |
40 |
400 |
4000 |
40000 |
2 |
50 |
500 |
5000 |
50000 |
4 |
64 |
625 |
6250 |
62500 |
5 |
80 |
800 |
8000 |
100000 |
8 |
100 |
1000 |
10000 |
125000 |
10 |
125 |
1250 |
12500 |
200000 |
16 |
160 |
1600 |
15625 |
250000 |
20 |
200 |
2000 |
20000 |
500000 |
25 |
250 |
2500 |
25000 |
1000000 |
32 |
320 |
3125 |
31250 |
If you specify a multiple-expression for the day, week, month, quarter, or year date parts, SAP Sybase IQ assumes the intervals started at the smallest date value (0001-01-01), smallest time value (00:00:00.000000), or smallest date-time value (0001-01-01.00:00:00.000000). For example, if you specify a multiple of 10 days, then SAP Sybase IQ calculates 10-day intervals starting at 0001-01-01.
For the day, week, month, quarter, or year date parts, you need not specify a multiple that divides evenly into the next larger unit of time granularity.
If SAP Sybase IQ rounds to a multiple of the week date part, the date value is always Sunday.