The DATETIME Conversion Option

Use the DATETIME conversion option to insert ASCII data that is stored in a fixed format into a TIME, TIMESTAMP, or DATETIME column.

This option converts the ASCII data input to binary and specifies the format of the input data. (The DATETIME format is used internally to interpret the input; it does not affect the storage or output format of the data.)

Note: For compatibility with earlier versions, you can specify that a column contains DATETIME data. However, such data is stored internally as the equivalent format, TIMESTAMP.

Here is the syntax:

DATETIME ('input-datetime-format')

In this UNIX example, slashes are separators in the date portion of the input data, and colons are separators in the time portion:

LOAD TABLE lineitem(
    l_quantity ASCII(4),
    l_shipdate DATETIME('MM/DD/YY hh:mm:ss'),
FILLER(1))
FROM '/d1/MILL1/tt.t'
PREVIEW ON

In this UNIX example, the FILLER(1) clause prevents SAP Sybase IQ from inserting a NULL in the next column (VWAP) after the DATETIME column:

LOAD TABLE snapquote_stats_base
SYMBOL ‘\x09’,
snaptime DATETIME('MM/DD/YY hh:mm:ss'),
FILLER(1))
VWAP ‘\x09’,
RS_DAY ‘\x09’,
FROM '/d1/MILL1/tt.t'
PREVIEW ON

In this UNIX example, the destination columns contain TIME data, but the input data is DATETIME. Use the DATETIME conversion option, and use the FILLER clause to skip over the date portion.

LOAD TABLE Customers(
    open_time DATETIME('hh:mmaa'),
    close_time DATETIME('hh:mmaa'),
FILLER(9))
FROM '/d1/MILL1/tt.t'
PREVIEW ON