DATECEILING function [Date and time]

Calculates a new date, time, or datetime value by increasing the provided value up to the nearest larger value of the specified granularity.

DATECEILING ( date-part, datetime-expression [,multiple -expression])

The following date parts are not compatible with DATECEILING:

• DayofYear

• WeekDay

• CalYearofWeek

• CalWeekofYear

• CalDayofWeek

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, Sybase IQ generates an error. If multiple-expression evaluates to a NULL, then the function result is NULL.

This statement returns the value August 13, 2009 10:40.00.000AM:

SELECT DATECEILING( MI, 'August 13, 2009, 10:32.00.132AM', 10) FROM iq_dummy

This statement returns the value August 13, 2009 10:32.35.456800 AM:

SELECT DATECEILING( US, 'August 13, 2009, 10:32.35.456789AM', 200 ) FROM iq_dummy

This statement returns the value August 13, 2009 10:32.35.600000 AM:

SELECT DATECEILING( US, 'August 13, 2009, 10:32.35.456789AM', 200000 ) FROM iq_dummy

This statement returns the value August 13, 2009 10:32.35.456789 AM:

SELECT DATECEILING( US, 'August 13, 2009, 10:32.35.456789AM') FROM iq_dummy

This function calculates a new date, time, or datetime value by increasing the provided value up to the nearest larger value with the specified granularity. If you include the optional multiple-expression parameter, then the function increases the date and time up 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.

If you specify a multiple-expression for the microsecond, millisecond, second, minute, or hour date parts, Sybase IQ assumes that the multiple applies from the start of the next larger unit of granularity:

• 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, 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, 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, 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 Sybase IQ rounds to a multiple of the week date part, the date value is always Sunday.