Hitachi

Hitachi Advanced Database SQL Reference

8.9.7 ROUND

Return the datetime data rounded to the unit specified in the datetime format.

For the scalar function ROUND that is used to round numeric data, see 8.4.9 ROUND.

Organization of this subsection

(1) Specification format

scalar-function-ROUND ::= ROUND(datetime-data,datetime-format)
 
  datetime-data ::= value-expression
  datetime-format ::= literal

To Page Top

(2) Explanation of specification format

datetime-data:

Specifies the datetime data to be rounded.

The following rules apply:

  • Specify the datetime data in the form of a value expression. For details about value expressions, see 7.20 Value expression.

  • The data type of the datetime data must be DATE, TIME, or TIMESTAMP.

  • You cannot specify a dynamic parameter by itself for the datetime data.

datetime-format:

Specifies the units of the datetime data to be rounded.

The following rules apply:

  • Specify a character string literal for datetime-format. For details about character string literals, see 6.3 Literals.

  • The following table shows the elements that can be specified in the datetime format.

    Table 8‒28: Elements that can be specified in the datetime format

    No.

    Element that can be specified in the datetime format

    Unit

    Description

    1

    CC

    Century

    If the datetime data is on or after the 51st year of the century, it is rounded up to January 1 00:00:00 of the first year of the next century. If it is on or before the 50th year, it is rounded down to January 1 00:00:00 of the first year of the same century.

    • Example of rounding up

      ROUND(TIMESTAMP'1951-10-04 15:25:38','CC')

      → TIMESTAMP'2001-01-01 00:00:00'

    • Example of rounding down

      ROUND(TIMESTAMP'1950-10-04 15:25:38','CC')

      → TIMESTAMP'1901-01-01 00:00:00'

    2

    YYYY

    YYYYN

    YY

    YYN

    Year

    If the datetime data is on or after July 1, it is rounded up to January 1 00:00:00 of the next year. If it is on or before June 30, it is rounded down to January 1 00:00:00 of the same year.

    • Example of rounding up

      ROUND(TIMESTAMP'2013-07-01 15:25:38','YYYY')

      → TIMESTAMP'2014-01-01 00:00:00'

    • Example of rounding down

      ROUND(TIMESTAMP'2013-06-30 15:25:38','YYYY')

      → TIMESTAMP'2013-01-01 00:00:00'

    3

    Q

    Quarter

    If the datetime data is on or after the 16th of the second month of the quarter (February, May, August, or November), it is rounded up to 00:00:00 on the first day of the first month of the next quarter. If it is on or before the 15th, it is rounded down to 00:00:00 on the first day of the first month of the same quarter.

    • Example of rounding up

      ROUND(TIMESTAMP'2013-11-16 15:25:38','Q')

      → TIMESTAMP'2014-01-01 00:00:00'

    • Example of rounding down

      ROUND(TIMESTAMP'2013-11-15 15:25:38','Q')

      → TIMESTAMP'2013-10-01 00:00:00'

    Quarters are assumed to be three months long, starting on January 1.

    • First quarter: January 1 - March 31

    • Second quarter: April 1 - June 30

    • Third quarter: July 1 - September 30

    • Fourth quarter: October 1 - December 31

    4

    MONTH

    MON

    MM

    Month

    If the datetime data is on or after the 16th, it is rounded up to 00:00:00 on the first day of the next month. If it is on or before the 15th, it is rounded down to 00:00:00 on the first day of the same month.

    • Example of rounding up

      ROUND(TIMESTAMP'2014-01-16 15:25:38','MONTH')

      → TIMESTAMP'2014-02-01 00:00:00'

    • Example of rounding down

      ROUND(TIMESTAMP'2014-01-15 15:25:38','MONTH')

      → TIMESTAMP'2014-01-01 00:00:00'

    5

    WW

    Week

    The first day of the week is assumed to be the day of the week of the first day of the same year.

    If the datetime data is on or after 12:00 noon on the fourth day from the start of the week, it is rounded up to 00:00:00 on the first day of the next week. If it is before 12:00 noon on the fourth day, it is rounded down to 00:00:00 on the first day of the same week.

    • Example of rounding up

      ROUND(TIMESTAMP'2014-01-04 15:25:38','WW')

      → TIMESTAMP'2014-01-08 00:00:00'

      Because January 1, 2014 falls on a Wednesday, the first day of the week is taken to be Wednesday. The example is on or after 12:00 noon on the fourth day of that week (Saturday January 4), and so is rounded up to 00:00:00 on the first day of the next week (January 8).

    • Example of rounding down

      ROUND(TIMESTAMP'2014-01-04 10:25:38','WW')

      → TIMESTAMP'2014-01-01 00:00:00'

      Because January 1, 2014 falls on a Wednesday, the first day of the week is taken to be Wednesday. The example is before 12:00 noon on the fourth day of that week (Saturday January 4), and so is rounded down to 00:00:00 on the first day of the same week (January 1).

    6

    W

    Week

    The first day of the week is assumed to be the day of the week of the first day of the same month.

    If the datetime data is on or after 12:00 noon on the fourth day from the start of the week, it is rounded up to 00:00:00 on the first day of the next week. If it is before 12:00 noon on the fourth day, it is rounded down to 00:00:00 on the first day of the same week.

    • Example of rounding up

      ROUND(TIMESTAMP'2014-02-04 12:25:38','W')

      → TIMESTAMP'2014-02-08 00:00:00'

      Because February 1, 2014 falls on a Saturday, the first day of the week is taken to be Saturday. The example is on or after 12:00 noon on the fourth day of that week (Tuesday, February 4), and so is rounded up to 00:00:00 on the first day of the next week (February 8).

    • Example of rounding down

      ROUND(TIMESTAMP'2014-02-04 11:55:38','W')

      → TIMESTAMP'2014-02-01 00:00:00'

      Because February 1, 2014 falls on a Saturday, the first day of the week is taken to be Saturday. The example is before 12:00 noon on the fourth day of that week (Tuesday, February 4), and so is rounded down to 00:00:00 on the first day of the same week (February 1).

    7

    DAY

    DAYN

    DY

    DYN

    D

    Week

    The first day of the week is defined as Sunday.

    If the datetime data is on or after 12:00 noon on the fourth day (Wednesday) from the start of the week, it is rounded up to 00:00:00 on the first day of the next week. If it is before 12:00 noon on the fourth day, it is rounded down to 00:00:00 on the first day of the same week.

    • Example of rounding up

      ROUND(TIMESTAMP'2014-02-05 12:25:38','DAY')

      → TIMESTAMP'2014-02-09 00:00:00'

      February 5, 2014 falls on a Wednesday. Therefore, it is rounded up to 00:00:00 on February 9 (Sunday).

    • Example of rounding down

      ROUND(TIMESTAMP'2014-02-05 11:55:38','DAY')

      → TIMESTAMP'2014-02-02 00:00:00'

      February 5, 2014 falls on a Wednesday. Therefore, it is rounded down to 00:00:00 on February 2 (Sunday).

    8

    DD

    DDD

    Day

    If the datetime data is on or after 12:00 noon, it is rounded up to 00:00:00 on the next day. If it is before 12:00 noon, it is rounded down to 00:00:00 on the same day.

    • Example of rounding up

      ROUND(TIMESTAMP'2014-01-16 15:25:38','DD')

      → TIMESTAMP'2014-01-17 00:00:00'

    • Example of rounding down

      ROUND(TIMESTAMP'2014-01-16 10:25:38','DD')

      → TIMESTAMP'2014-01-16 00:00:00'

    9

    HH

    HH12

    HH24

    Hour

    If the datetime data is on or after the 30 minute mark, it is rounded up to the beginning of the next hour. If it is on or before the 29 minute mark, it is rounded down to the beginning of the same hour.

    • Example of rounding up

      ROUND(TIMESTAMP'2014-01-16 15:35:38','HH')

      → TIMESTAMP'2014-01-16 16:00:00'

    • Example of rounding down

      ROUND(TIMESTAMP'2014-01-16 15:25:38','HH')

      → TIMESTAMP'2014-01-16 15:00:00'

    10

    MI

    Minute

    If the datetime data is on or after the 30 second mark, it is rounded up to the beginning of the next minute. If it is on or before the 29 second mark, it is rounded down to the beginning of the same minute.

    • Example of rounding up

      ROUND(TIMESTAMP'2014-01-16 15:35:33','MI')

      → TIMESTAMP'2014-01-16 15:36:00'

    • Example of rounding down

      ROUND(TIMESTAMP'2014-01-16 15:35:28','MI')

      → TIMESTAMP'2014-01-16 15:35:00'

    11

    SSSSS

    SS

    Second

    If the datetime data is on or after the 500 millisecond mark, it is rounded up to the beginning of the next second. If it is before the 500 millisecond mark, it is rounded down to the beginning of the same second.

    • Example of rounding up

      ROUND(TIME'11:59:30.596123','SS')

      → TIME'11:59:31.000000'

    • Example of rounding down

      ROUND(TIME'11:59:30.488123','SS')

      → TIME'11:59:30.000000'

  • The datetime format must be specified as single-byte character string data. Uppercase and lowercase letters are treated the same.

  • In the cases where multiple datetime format elements are listed, the execution results will be the same regardless of which alternative is specified. For example, you can specify YYYY or YYYYN and the execution results will be the same.

  • Spaces before and after the datetime format element are ignored.

  • The length of the datetime format cannot exceed 64 bytes.

To Page Top

(3) Rules

  1. The data type and data length of the execution result are shown in the following table.

    Table 8‒29: Data type and data length of the execution result of the scalar function ROUND

    Data type and data length of the datetime data

    Data type and data length of the execution result

    DATE

    DATE

    TIME(p)

    TIME(p)

    TIMESTAMP(p)

    TIMESTAMP(p)

    Legend: p: Fractional seconds precision

  2. The NOT NULL constraint does not apply to the value of the execution result (the null value is allowed).

  3. If the datetime data has a null value, the execution result will be a null value.

  4. When the type of datetime-data is DATE but the datetime format is an element for rounding based on time within the day (DDD, DD, HH, HH12, HH24, MI, SSSSS, or SS), the original datetime data is returned unchanged.

  5. When you specify TIME type data as the datetime data, you cannot specify the non-time elements in the datetime format (CC, YYYY, YYYYN, YY, YYN, Q, MONTH, MON, MM, WW, W, DAY, DAYN, DY, DYN, D, DDD, and DD).

  6. When the type of datetime-data is DATE, 00:00:00 is assumed for the time elements. This is why the week is rounded down (not up) in the following example.

    Example

    ROUND(DATE'2013-10-04','W') → DATE'2013-10-01'

  7. If the data type of the execution result is DATE, an error occurs if the execution result falls outside the range January 1, 0001 to December 31, 9999.

  8. If the data type of the execution result is TIME, an error occurs if the execution result falls outside the range 00:00:00.000000000000 to 23:59:59.999999999999.

  9. If the data type of the execution result is TIMESTAMP, an error occurs if the execution result falls outside the range January 1, 0001 00:00:00.000000000000 to December 31, 9999 23:59:59.999999999999.

To Page Top

(4) Example

Example:

From the sales history table (SALESLIST), retrieve the quantities purchased in 2013 of product code (PUR-CODE) P001, and group the results into six-month periods (January 1 to June 30 and July 1 to December 31).

SELECT SUM("PUR-NUM") FROM "SALESLIST"
    WHERE "PUR-DATE" BETWEEN DATE'2013-01-01' AND DATE'2013-12-31'
    AND "PUR-CODE"='P001'
    GROUP BY ROUND("PUR-DATE",'YYYY')

[Figure]

When element YYYY is specified for the datetime format, data from July 1 and later is rounded up, and data from June 30 and earlier is rounded down. The data can therefore be grouped into six-month periods, with 1/1 - 6/30 as the first half and 7/1 - 12/31 as the second half.

To Page Top

All Rights Reserved. Copyright (C) 2012, 2020, Hitachi, Ltd.