TRUNCATE numeric:
.-,--0-------------------.
>>-+-TRUNCATE-+--(--numeric-expression1--+------------------------+--)-><
'-TRUNC----' '-,--numeric-expression2-'
TRUNCATE datetime:
.-,--'DD'---------------------------.
>>-+-TRUNCATE-+--(--datetime-expression--+-----------------------------------+--)-><
'-TRUNC----' '-,--format-string--+-------------+-'
'-locale-name-'
The schema is SYSIBM. The SYSFUN version of the TRUNCATE numeric function continues to be available.
The
TRUNCATE function returns a truncated value of:
- A number, truncated to the specified number of places to the right
or left of the decimal point, if the result of the first argument
is a numeric value
- A datetime value, truncated to the unit specified
by format-string, if the first argument
is a DATE, TIME, or TIMESTAMP
- TRUNCATE numeric
- If numeric-expression1 has
a numeric data type, the TRUNCATE function returns numeric-expression1 truncated
to numeric-expression2 places to the right
of the decimal point if numeric-expression2 is
positive, or to the left of the decimal point if numeric-expression2 is
zero or negative. If numeric-expression2 is
not specified, numeric-expression1 is truncated
to zero places left of the decimal point.
- numeric-expression1
- An
expression that must return a value that is a built-in CHAR, VARCHAR,
GRAPHIC, VARGRAPHIC, or numeric data type. If the value is not a numeric
data type, it is implicitly cast to DECFLOAT(34) before evaluating
the function.
If the expression is a decimal floating-point data
type, the DECFLOAT rounding mode will not be used. The rounding behavior
of TRUNCATE corresponds to a value of ROUND_DOWN. If a different rounding
behavior is wanted, use the QUANTIZE function.
- numeric-expression2
- An expression that returns a value that is a built-in
numeric data type. If the value is not of type INTEGER, it is implicitly
cast to INTEGER before evaluating the function.
If numeric-expression2 is
not negative, numeric-expression1 is truncated
to the absolute value of numeric-expression2 number
of places to the right of the decimal point.
If
numeric-expression2 is
negative,
numeric-expression1 is truncated
to the absolute value of
numeric-expression2 +
1 number of places to the left of the decimal point. If the absolute
value of a negative
numeric-expression2 is
larger than the number of digits to the left of the decimal point,
the result is 0. For example:
TRUNCATE(748.58,-4) = 0
The data type, length, and scale attributes of the
result are the same as the data type, length, and scale attributes
of the first argument.
If either argument can
be null, the result can be null. If either argument is null, the result
is the null value.
- TRUNCATE datetime
- If datetime-expression has a datetime
data type, the TRUNCATE function returns datetime-expression rounded
to the unit specified by the format-string.
If format-string is not specified, datetime-expression is
truncated to the nearest day, as if 'DD' is specified for format-string.
- datetime-expression
- An expression that must return a value that is a DATE, a TIME,
or a TIMESTAMP. String representations of these data types are not
supported and must be explicitly cast to a DATE, TIME, or TIMESTAMP
for use with this function; alternatively, you
can use the TRUNC_TIMESTAMP function for a string representation of
a date or timestamp.
- format-string
- An
expression that returns a built-in character string data type with
an actual length that is not greater than 254 bytes. The format element
in format-string specifies how datetime-expression should
be truncated. For example, if format-string is
'DD', a timestamp that is represented by datetime-expression is
truncated to the nearest day. Leading and trailing blanks are removed
from the string, and the resulting substring must be a valid format
element for the type of datetime-expression (SQLSTATE
22007). The default is 'DD', which cannot be used if the data type
of datetime-expression is TIME.
Allowable
values for format-string are listed in the
table of format elements found in the description of the ROUND function.
- locale-name
- A character constant that specifies the locale used to determine
the first day of the week when using format element values
DAY, DY, or D. The value of locale-name is
not case sensitive and must be a valid locale (SQLSTATE 42815). For
information on valid locales and their naming, see "Locale names
for SQL and XQuery". If locale-name is
not specified, the value of the special register CURRENT LOCALE LC_TIME
is used.
The result of the function has
the same date type as datetime-expression.
The result can be null; if any argument is null, the result is the
null value.
The data type and length attribute of the result are the
same as the data type and length attribute of the first argument.
The result can be null if the argument can be null or if
the argument is not a decimal floating-point number and the database
is configured with dft_sqlmathwarn set to YES;
the result is the null value if the argument is null.
Notes
- Determinism: TRUNCATE is
a deterministic function. However, the following invocations of the
function depend on the value of the special register CURRENT LOCALE
LC_TIME.
- Truncate of a datetime value when locale-name is
not explicitly specified and one of the following is true:
- format-string is not a constant
- format-string is a constant and includes
format elements that are locale sensitive
Invocations of the function that depend on the value of a special
register cannot be used wherever special registers cannot be used.
Examples
- Using the EMPLOYEE table, calculate the average monthly salary
for the highest paid employee. Truncate the result two places to the
right of the decimal point.
SELECT TRUNCATE(MAX(SALARY)/12,2)
FROM EMPLOYEE;
Assuming the highest paid employee
earns $52750.00 per year, the example returns 4395.83.
- Display the number 873.726 truncated 2, 1, 0, -1, and -2 decimal
places, respectively.
VALUES (
TRUNCATE(873.726,2),
TRUNCATE(873.726,1),
TRUNCATE(873.726,0),
TRUNCATE(873.726,-1),
TRUNCATE(873.726,-2),
TRUNCATE(873.726,-3) );
This example returns
873.720, 873.700, 873.000, 870.000, 800.000, and 0.000.
- Display the decimal-floating point number 873.726
truncated 0 decimal places.
VALUES(TRUNCATE(DECFLOAT(873.726),0))
Returns
the value 873.
- Set the variable vTRNK_DT with the input date
rounded to the nearest month value.
SET vTRNK_DT = TRUNC(DATE('2000-08-16'), 'MONTH');
The
value set is 2000-08-01.
- Set the host variable TRNK_TMSTMP with the current
year rounded to the nearest year value.
SET :TRNK_TMSTMP = TRUNCATE('2000-03-14-17.30.00'), 'YEAR');
The
value set is 2000-01-01-00.00.00.000000.