docs.intersystems.com
Home  /  Application Development: Core Topics  /  InterSystems SQL Reference  /  SQL Functions  /  TRUNCATE


InterSystems SQL Reference
TRUNCATE
[Back]  [Next] 
InterSystems: The power behind what matters   
Search:  


A scalar numeric function that truncates a number at a specified number of digits.
Synopsis
{fn TRUNCATE(numeric-expr,scale)}
Arguments
numeric-expr The number to be truncated. A number or numeric expression.
scale An expression that evaluates to an integer that specifies the number of places to truncate, counting from the decimal point. Can be zero, a positive integer, or a negative integer. If scale is a fractional number, InterSystems IRIS rounds it to the nearest integer.
TRUNCATE returns either the NUMERIC or DOUBLE data type. If numeric-expr is data type DOUBLE, TRUNCATE returns DOUBLE; otherwise, it returns NUMERIC.
Description
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.
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. However, 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.
TRIM can be used to perform a similar truncation operation on strings.
TRUNCATE can only be used as an ODBC scalar function (with the curly brace syntax).
Examples
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()
it returns 0.
See Also