CODEWITHSUNDEEP
databasedate-formatnetezza
user5502892
user5502892

Reputation: 359

Error of importing data from txt file to IBM netezza SQL database due to date format

I need to upload data from a text file to the Netezza table. It is not working because date format is not same between the Netezza table and Text file. As per Netezza table date format which should be date only but in text file it is datetime.

Is there any way, so that I can convert the datatime into date only while uploading.

  1. Below is the file format with one row of data -

AS_OF_DATE|ID
10/01/2015 00:00:00|40

  1. Below Netezza query I am using to upload

INSERT INTO LND_FINANCE_CUSTOMER (AS_OF_DATE,ID)

SELECT * FROM EXTERNAL 'D:123.txt'

USING ( QUOTEDVALUE DOUBLE DELIMITER '|' MAXERRORS 4 DATESTYLE MDY DATEDELIM '/' MAXROWS 0 Y2BASE 2000 ENCODING internal REMOTESOURCE ODBC ESCAPECHAR '\');

  1. 0 Rows are inserting because of date format. If in the text file I manually change the datetime format into date only. then it is working fine.

Upvotes: 1

Views: 899

Answers (1)

Niederee
Niederee

Reputation: 4295

If you force the file specification to timestamp the load should work fine. It will cast back to a date on the insert into LND_FINANCE_CUSTOMER. See Code Below:

    INSERT INTO LND_FINANCE_CUSTOMER (AS_OF_DATE,ID)

    SELECT * FROM EXTERNAL 'D:\123.txt'
(AS_OF_DATE  timestamp
        ,ID  integer)    
    USING ( QUOTEDVALUE DOUBLE DELIMITER '|' MAXERRORS 4 DATESTYLE MDY DATEDELIM '/' MAXROWS 0 Y2BASE 2000 ENCODING internal REMOTESOURCE ODBC ESCAPECHAR '\');

Upvotes: 1

Related Questions