InterSystems SQL Reference
TRUNCATE
|
|
A scalar numeric function that truncates a number at a specified number of digits.
Synopsis
{fn TRUNCATE(numeric-expr,scale)}
TRUNCATE truncates
numeric-expr by truncating at the
scale number of digits from the decimal point. It does not round numbers or add padding zeroes. Leading and trailing zeroes are removed before the
TRUNCATE operation.
-
If
scale is a positive number, truncation is performed at that number of digits to the right of the decimal point. If
scale is equal to or larger than the number of decimal digits, no truncation or zero filling occurs.
-
If
scale is zero, the number is truncated to a whole integer. In other words, truncation is performed at zero digits to the right of the decimal point; all decimal digits and the decimal point itself are truncated.
-
If
scale is a negative number, truncation is performed at that number of digits to the left of the decimal point. If
scale is equal to or larger than the number of integer digits in the number, zero is returned.
-
If
numeric-expr is zero (however expressed: 00.00, -0, etc.)
TRUNCATE returns 0 (zero) with no decimal digits, regardless of the
scale value.
-
TRUNCATE can only be used as an ODBC scalar function (with the curly brace syntax).
ROUND can be used to perform a similar truncation operation on numbers.
TRIM can be used to perform a similar truncation operation on strings.
TRUNCATE, ROUND, and $JUSTIFY
TRUNCATE and
ROUND are numeric functions that perform similar operations; they both can be used to decrease the number of significant decimal or integer digits of a number.
ROUND allows you to specify either rounding (the default), or truncation;
TRUNCATE does not perform rounding.
ROUND returns the same data type as
numeric-expr;
TRUNCATE returns
numeric-expr as data type NUMERIC, unless
numeric-expr is data type DOUBLE, in which case it returns data type DOUBLE.
TRUNCATE truncates to a specified number of fractional digits. If the truncation results in trailing zeros, these trailing zeros are preserved. However, if
scale is larger than the number of fractional decimal digits in the canonical form of
numeric-expr,
TRUNCATE does not zero-pad.
ROUND rounds (or truncates) to a specified number of fractional digits, but its return value is always normalized, removing trailing zeros. For example,
ROUND(10.004,2) returns 10, not 10.00.
Use
$JUSTIFY when rounding to a fixed number of fractional digits is important — for example, when representing monetary amounts.
$JUSTIFY returns the specified number of trailing zeros following the rounding operation. When the number of digits to round is larger than the number of fractional digits,
$JUSTIFY zero-pads.
$JUSTIFY also right-aligns the numbers, so that the DecimalSeparator characters align in a column of numbers.
$JUSTIFY does not truncate.
The following two examples both truncate a number to two decimal digits. The first (using Dynamic SQL) specifies
scale as an integer; the second (using Embedded SQL) specifies
scale as a host variable that resolves to an integer:
SET myquery = "SELECT {fn TRUNCATE(654.321888,2)} AS trunc"
SET tStatement = ##class(%SQL.Statement).%New()
SET qStatus = tStatement.%Prepare(myquery)
IF qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT}
SET rset = tStatement.%Execute()
DO rset.%Display()
SET x=2
&sql(SELECT {fn TRUNCATE(654.321888,:x)}
INTO :a)
IF SQLCODE'=0 {
WRITE !,"Error code ",SQLCODE }
ELSE {
WRITE !,"truncated value is: ",a }
both examples return 654.32 (truncation to two decimal places).
The following Dynamic SQL example specifies a
scale larger than the number of decimal digits:
SET myquery = "SELECT {fn TRUNCATE(654.321000,9)} AS trunc"
SET tStatement = ##class(%SQL.Statement).%New()
SET qStatus = tStatement.%Prepare(myquery)
IF qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT}
SET rset = tStatement.%Execute()
DO rset.%Display()
it returns 654.321 (InterSystems IRIS removed the trailing zeroes before the truncation operation; no truncation or zero padding occurred).
The following Dynamic SQL example specifies a
scale of zero:
SET myquery = "SELECT {fn TRUNCATE(654.321888,0)} AS trunc"
SET tStatement = ##class(%SQL.Statement).%New()
SET qStatus = tStatement.%Prepare(myquery)
IF qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT}
SET rset = tStatement.%Execute()
DO rset.%Display()
it returns 654 (all decimal digits and the decimal point are truncated).
The following Dynamic SQL example specifies a negative
scale:
SET myquery = "SELECT {fn TRUNCATE(654.321888,-2)} AS trunc"
SET tStatement = ##class(%SQL.Statement).%New()
SET qStatus = tStatement.%Prepare(myquery)
IF qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT}
SET rset = tStatement.%Execute()
DO rset.%Display()
it returns 600 (two integer digits have been truncated and replaced by zeroes; note that no rounding has been done).
The following Dynamic SQL example specifies a negative
scale as large as the integer portion of the number:
SET myquery = "SELECT {fn TRUNCATE(654.321888,-3)} AS trunc"
SET tStatement = ##class(%SQL.Statement).%New()
SET qStatus = tStatement.%Prepare(myquery)
IF qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT}
SET rset = tStatement.%Execute()
DO rset.%Display()