8.9.8 TRUNC
Returns the datetime data truncated to the unit specified in the datetime format.
For the scalar function TRUNC that is used to truncate numeric data, see 8.4.12 TRUNC.
- Organization of this subsection
(1) Specification format
scalar-function-TRUNC ::= TRUNC(datetime-data,datetime-format) datetime-data ::= value-expression datetime-format ::= literal
(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.
-
(3) Rules
-
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
-
The NOT NULL constraint does not apply to the value of the execution result (the null value is allowed).
-
If the datetime data has a null value, the execution result will be a null value.
-
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.
-
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).
-
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.
(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.