Sybase 12.4.2 User Manual
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.
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.
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
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.
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.