DATEROUND Function [Date and Time]

Calculates a new date, time, or datetime value by rounding the provided value up or down to the nearest multiple of the specified value with the specified granularity.

Syntax

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

Parameters

Parameter

Description

date part

The date part to be returned.

datetime-expression

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

Examples

This statement returns the value August 13, 2009, 10:30.000AM:
SELECT DATEROUND( MI, 'August 13, 2009 10:33.123AM', 10) FROM iq_dummy
This statement returns the value August 13, 2009 10:32:35.456600 AM:
SELECT DATEROUND( US, 'August 13, 2009, 10:32:35.456500AM', 200 ) FROM iq_dummy
This statement returns the value August 13, 2009 10:32:35.456789 AM:
SELECT DATEROUND( US, 'August 13, 2009, 10:32:35.456789AM') FROM iq_dummy
This statement returns the value August 13, 2009 10:32:35.456400 AM:
SELECT DATEROUND( US, 'August 13, 2009, 10:32:35.456499AM', 200 ) FROM iq_dummy

Usage

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

The following date parts are not compatible with DATEROUND:
  • DayofYear

  • WeekDay

  • CalYearofWeek

  • CalWeekofYear

  • CalDayofWeek

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, then 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, then the date value is always Sunday.

Standards and Compatibility

  • SQL—Vendor extension to ISO/ANSI SQL grammar.

  • Sybase—Not supported by Adaptive Server Enterprise or SQL Anywhere.

Related reference
DATEADD Function [Date and Time]
DATECEILING Function [Date and Time]
DATEDIFF Function [Date and Time]
DATEFLOOR Function [Date and Time]
DATEPART Function [Date and Time]
DATENAME Function [Date and Time]
Date Parts