Data Types Required

Text

Text types contain letters, numbers and punctuation marks, including spaces; it is a sequence of zero or more printable characters.  If the field is required, this field must contain at least one non-blank character. If it is not required, it may be empty or a sequence of printable characters.

Text types may also contain some non-printable characters (such as newlines (ASCII 10), carriage returns (ASCII 13) and tabs (ASCII 8)) but precautions must be taken to ensure the file format can handle the non-printable characters.  For example, if a newline is required to be part of a text field, a CSV-formatted file must be used and the field surrounded with quotes; however, it is recommended that these non-printable characters be eliminated from the load files to produce more reliable data loads.

It is important to note that leading and trailing spaces (and other whitespace characters) are trimmed from Text fields.

If a text field is found to contain non-printable characters, then the field can either map the non-printable character to a space (squeezing multiple spaces into a single space) or reject the entire load file.  This determination must be provided prior to load of the file.  The default behavior is to reject the entire load file.

Decimal

The decimal data type can take two forms: decimal(p) floating point and decimal(p,s) fixed point.  In an ANSI-compliant database, however, all decimal numbers are fixed point.  By default, literal numbers that include a decimal ( . ) point are interpreted by the database server as decimal values.  The decimal data type stores decimal floating-point numbers up to a maximum of 32 significant digits, where p is the total number of significant digits (the precision).  In fixed-point numbers, decimal(p,s), the decimal point is fixed at a specific place, regardless of the value of the number.  When you specify a column of this type, you declare its precision (p) as the total number of digits that it can store, from 1 to 32.  You declare its scale (s) as the total number of digits in the fractional part (that is, to the right of the decimal point).

Numeric

The numeric data type is a synonym for fixed-point decimal.

Money

The money data type stores currency amounts.  Like the decimal(p,s) data type, money can store fixed-point numbers up to a maximum of 32 significant digits, where p is the total number of significant digits (the precision) and s is the number of digits to the right of the decimal point (the scale).

Unlike the decimal data type, the money data type is always treated as a fixed-point decimal number.  The database server defines the data type money(p) as decimal(p,2).  If the precision and scale are not specified, the database server defines a money column as decimal(16,2).

Integer

The integer data type stores whole numbers that range from -2,147,483,647 to 2,147,483,647, for 9 or 10 digits of precision.  The number 2,147,483,648 is a reserved value and cannot be used.  The integer value is stored as a signed binary integer and is typically used to store counts, quantities, and so on.

Character

The character data type stores any string of letters, numbers, and symbols.  It can store single-byte and multibyte characters, based on the database locale.

A character(n) column has a length of n bytes, where 1 = n = 32,767.  If you do not specify n, character(1) is the default length.  Character columns typically store alphanumeric strings, such as names, addresses, phone numbers, and so on.  When a value is retrieved or stored as character(n), exactly n bytes of data are transferred.  If the string is shorter than n bytes, the string is extended with blank spaces up to the declared length.  If the data value is longer than n bytes, a data string of length n that has been truncated from the right is inserted or retrieved, without the database server raising an exception.

Date/Time

The datetime data type stores an instant in time expressed as a calendar date and time of day.  You choose how precisely a datetime value is stored; its precision can range from a year to a fraction of a second.

datetime stores a data value as a contiguous series of fie

Have more questions? Submit a request

0 Comments

Please sign in to leave a comment.
Powered by Zendesk