Sybase Adaptive Server IQ 12.4.2, Administration And Performance Manual

Page: 230 / 536

Converting data on insertion
210
Substitution of NULL or blank characters
Adaptive Server IQ supports zero-length
CHAR
and
VARCHAR
data. If the
length of a
CHAR
or
VARCHAR
cell is zero and the cell is not NULL, you get a
zero-length cell.
For all other data types, if the length of the cell is zero, Adaptive Server IQ
inserts a NULL.
This treatment of zero-length character data is ANSI behavior. If you require
non-ANSI behavior, see the Non_Ansi_Null_Varchar option in the Adaptive
Server IQ Reference Manual.
The DATE Option
Use the DATE conversion option to insert ASCII data that is stored in a fixed
format into a DATE column. This option converts the ASCII data input to
binary and specifies the format of the input data. (The DATE format is used
internally to interpret the input; it does not affect the storage or output format
of the data.) See the
ASCII conversion format for more information.
Example
In this Windows NT example, data for the l_shipdate column is converted from
the specified format into binary. The 1–byte FILLER skips over carriage returns
in the input file.
LOAD TABLE lineitem(
l_orderkey NULLS(ZEROS) ASCII(4),
l_partkey ASCII(3),
l_shipdate DATE(’MM/DD/YY’),
l_suppkey ASCII(5),
FILLER(1))
FROM ’C:\\MILL1\\shipinfo.t’
PREVIEW ON
Specifying the DATE Format
Specify the format of the input data using y or Y for years, m or M for months,
d or D for days, and j or J for Julian days. The length of the format string is the
width of the input column. Table 5-7 describes the formatting options.
Table 5-7: Formatting dates
Option Meaning
yyyy or YYYY
yy or YY
Represents number of year. Default is 1900.