CODEWITHSUNDEEP
db2
AndersonOnoja
AndersonOnoja

Reputation: 11

Loading TIMESTAMP data in a file into IBM Db2 on Cloud

I am loading a data into IBM cloud and it has a date and time format that looks like this, 08/28/2004 05:50:56 PM. what would be the correct time format? i have tried given available formats but they do not match. If i need to create one, how do i go about it? Thank you.

Upvotes: 1

Views: 1760

Answers (2)

Paul Vernon
Paul Vernon

Reputation: 3901

I believe that the Db2 Console for Db2 on Cloud currently uses LOAD statements to get data into Db2. The Db2 Console for Db2 Warehouseon Cloud uses both LOAD and EXTERNAL TABLE (for Netezza/PDA sourced data)

The timestamp format options for LOAD are less flexible than those available with EXTERNAL TABLE based loads in Db2 11.5

You need to use this format

MM/DD/YYYY HH:MM:SS TT

as per the available elements listed under the timestampformat option on this page https://www.ibm.com/support/knowledgecenter/en/SSEPGG_11.5.0/com.ibm.db2.luw.admin.cmd.doc/doc/r0008305.html

Here is an client command line example.

$ cat file.csv
08/28/2004 05:50:56 PM
$ db2 "create table ts(ts timestamp)"
DB20000I  The SQL command completed successfully.

$ db2 'import from file.csv of del modified by timestampformat="MM/DD/YYYY HH:MM:SS TT" insert into ts'
SQL3109N  The utility is beginning to load data from file "file.csv".

SQL3110N  The utility has completed processing.  "1" rows were read from the 
input file.

SQL3221W  ...Begin COMMIT WORK. Input Record Count = "1".

SQL3222W  ...COMMIT of any database changes was successful.

SQL3149N  "1" rows were processed from the input file.  "1" rows were 
successfully inserted into the table.  "0" rows were rejected.


Number of rows read         = 1
Number of rows skipped      = 0
Number of rows inserted     = 1
Number of rows updated      = 0
Number of rows rejected     = 0
Number of rows committed    = 1

$ db2 "select * from ts"

TS                        
--------------------------
2004-08-28-17.50.56.000000

  1 record(s) selected.

Upvotes: 2

Mark Barinstein
Mark Barinstein

Reputation: 12314

The following works:

values to_date('08/28/2004 05:50:56 PM', 'MM/DD/YYYY HH:MI:SS AM')

So, try this format.

Upvotes: 0

Related Questions