Precision of SQL data types

The precision of a numeric column or parameter refers to the maximum number of digits that are used by the data type of the column or parameter. The precision of a non-numeric column or parameter generally refers to the maximum length or the defined length of the column or parameter.

The following table defines the precision for each SQL data type.

Table 1. Precision of SQL data types
fSqlType Precision
  • SQL_CHAR
  • SQL_VARCHAR
  • SQL_CLOB
 The defined number of characters for the column or parameter. For example, the precision of a column defined as CHAR(10) is 10.
SQL_LONGVARCHAR The maximum length, in characters, of the column or parameter.1
  • SQL_DECIMAL
  • SQL_NUMERIC
 The defined maximum number of digits. For example, the precision of a column defined as NUMERIC(10,3) is 10.
SQL_DECFLOAT 16 if the column is defined as DECFLOAT(16). 34 if the column is defined as DECFLOAT(34).
SQL_SMALLINT2 5
SQL_INTEGER2 10
SQL_BIGINT2 19
SQL_FLOAT2 15
SQL_REAL2 7
SQL_ROWID 40
SQL_DOUBLE2 15
  • SQL_BINARY
  • SQL_VARBINARY
  • SQL_BLOB
 The defined length, in characters, of the column or parameter. For example, the precision of a column defined as CHAR(10) FOR BIT DATA, is 10.
SQL_LONGVARBINARY The maximum length, in characters, of the column or parameter.
SQL_TYPE_DATE2 10 (the number of characters in the yyyy-mm-dd format).
SQL_TYPE_TIME2 8 (the number of characters in the hh:mm:ss format).
SQL_TYPE_TIMESTAMP The number of characters in the #yyyy-mm-dd hh:mm:ss[.ffffffffffff]” or #yyyy-mm-dd.hh.mm.ss[.ffffffffffff]” format that is used by the TIMESTAMP data type. For example, if a timestamp does not use seconds or fractional seconds, the precision is 16 (the number of characters in the "yyyy-mm-dd hh:mm” format). If a timestamp uses millionths of a second, the precision is 26 (the number of characters in the "yyyy-mm-dd hh:mm:ss.ffffff” format). The maximum for fractional seconds is 12 digits.
SQL_TYPE_TIMESTAMP_WITH_TIMEZONE The number of characters in the "yyyy-mm-dd hh:mm:ss[.ffffffffffff]±hh:mm" or "yyyy-mm-dd-hh.mm.ss[.ffffffffffff]±hh:mm" format used by the TIMESTAMP(integer) WITH TIME ZONE data type. If the scale of the timestamp is 0, the precision is 25 (19 bytes timestamp followed by 6 bytes time zone). If the scale is greater than 0, the precision is 26 (an extra period to separate seconds from fractional seconds) plus the scale of the timestamp.
SQL_GRAPHIC SQL_VARGRAPHIC SQL_DBCLOB The defined length, in characters, of the column or parameter. For example, the precision of a column defined as GRAPHIC(10) is 10.
SQL_LONGVARGRAPHIC The maximum length, in characters, of the column or parameter.
SQL_XML2 0
Notes:
  1. When defining the precision of a parameter of this data type with SQLBindParameter(), cbColDef should be set to the total length in bytes of the data, not the precision as defined in this table.
  2. The cbColDef argument of SQLBindParameter() is ignored for this data type.