The DATETIME conversion option

Use the DATETIME conversion option to insert ASCII data that is stored in a fixed format into a TIME or 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.) See the ASCII conversion format for more information.

For compatibility with previous releases, 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 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. You use the DATETIME conversion option, and use FILLER 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