8.9.8 TRUNC

(1) Specification format

scalar-function-TRUNC ::= TRUNC(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 truncated.

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 truncated.

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‒30: Elements that can be specified in the datetime format

  No.	Element that can be specified in the datetime format	Unit	Description
  1	CC	Century	The datetime data is rounded down to January 1 00:00:00 of the first year of the same century. Example TRUNC(TIMESTAMP'2014-03-14 15:25:38','CC') → TIMESTAMP'2001-01-01 00:00:00'
  2	YYYY YYYYN YY YYN	Year	The datetime data is rounded down to January 1 00:00:00 of the same year. Example TRUNC(TIMESTAMP'2014-03-14 15:25:38','YYYY') → TIMESTAMP'2014-01-01 00:00:00'
  3	Q	Quarter	The datetime data is rounded down to 00:00:00 on the first day of the first month of the same quarter. Example TRUNC(TIMESTAMP'2014-03-14 15:25:38','Q') → TIMESTAMP'2014-01-01 00:00:00' Quarters are assumed to be three months long, starting on January 1. First quarter: January 1 to March 31 Second quarter: April 1 to June 30 Third quarter: July 1 to September 30 Fourth quarter: October 1 to December 31
  4	MONTH MON MM	Month	The datetime data is rounded down to 00:00:00 on the first day of the same month. Example TRUNC(TIMESTAMP'2014-03-14 15:25:38','MONTH') → TIMESTAMP'2014-03-01 00:00:00'
  5	WW	Week	The datetime data is rounded down to 00:00:00 on the first day of the same week. The first day of the week is assumed to be the day of the week of the first day of the same year. Example TRUNC(TIMESTAMP'2014-03-14 15:25:38','WW') → TIMESTAMP'2014-03-12 00:00:00' Because January 1, 2014 falls on a Wednesday, the first day of the week is taken to be Wednesday. The datetime data is therefore rounded down to 00:00:00 on March 12, which is the first day (Wednesday) of that week.
  6	W	Week	The datetime data is rounded down to 00:00:00 on the first day of the same week. The first day of the week is assumed to be the day of the week of the first day of the same month. Example TRUNC(TIMESTAMP'2014-03-14 15:25:38','W') → TIMESTAMP'2014-03-08 00:00:00' Because March 1, 2014 falls on a Saturday, the first day of the week is taken to be Saturday. The datetime data is therefore rounded down to 00:00:00 on March 8, which is the first day (Saturday) of that week.
  7	DAY DAYN DY DYN D	Week	The datetime data is rounded down to 00:00:00 on the first day of the same week. The first day of the week is assumed to be Sunday. Example TRUNC(TIMESTAMP'2014-03-14 15:25:38','DAY') → TIMESTAMP'2014-03-09 00:00:00' March 14, 2014 falls on a Friday. The datetime data is therefore rounded down to 00:00:00 on March 9, which is the first day (Sunday) of that week.
  8	DD DDD	Day	The datetime data is rounded down to 00:00:00 on the same day. Example TRUNC(TIMESTAMP'2014-03-14 15:25:38','DD') → TIMESTAMP'2014-03-14 00:00:00'
  9	HH HH12 HH24	Hour	The datetime data is rounded down to the beginning of the same hour. Example TRUNC(TIMESTAMP'2014-03-14 15:25:38','HH') → TIMESTAMP'2014-03-14 15:00:00'
  10	MI	Minute	The datetime data is rounded down to the beginning of the same minute. Example TRUNC(TIMESTAMP'2014-03-14 15:25:38','MI') → TIMESTAMP'2014-03-14 15:25:00'
  11	SSSSS SS	Second	The datetime data is rounded down to the beginning of the same second. Example TRUNC(TIME'11:58:31.784','SS') → TIME'11:58:31.000'

• 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, specify one of them. 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‒31: Data type and data length of the execution result of the scalar function TRUNC

   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 the time of 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. An error results if you specify DAY, DAYN, DY, DYN, or D in the datetime format and the execution result is earlier than January 1, 0001.

To Page Top

(4) Example

Example:

From the sales history table (SALESLIST), retrieve the quantities of product code (PUR-CODE) P001 purchased in November 2013 and group the results by week.

SELECT SUM("PUR-NUM") FROM "SALESLIST"
    WHERE "PUR-DATE" BETWEEN DATE'2013-11-01' AND DATE'2013-11-30'
    AND "PUR-CODE"='P001'
    GROUP BY TRUNC("PUR-DATE",'DAY')

When you specify the element DAY for the datetime format, it groups the quantities purchased by week, using Sunday as the first day of the week.

To Page Top