Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2007
    Posts
    84

    Unanswered: Create Table with DATE

    I use DB2 9.7 LUW with AIX 6.1. I'm creating a new table with a Date field.
    I need to format the field so it accepts the data input. Here is the Create Table statement that I'm using now:

    CREATE TABLE "PRODDTA"."F5541021" (
    "MONTH_YEAR" DATE NOT NULL WITH DEFAULT,
    "BRANCH" GRAPHIC(4) NOT NULL WITH DEFAULT,
    "CUST_NO" DECIMAL(8,0) NOT NULL WITH DEFAULT,
    "CUSTOMER" VARCHAR(40),
    "INVENTORY_WEIGHT" DECIMAL(15,0) )
    IN "PRODDTAT4K" INDEX IN "PRODDTAI" LONG IN "PRODDTAL" NOT LOGGED INITIALLY ;

    I'm looking at the MONTH_YEAR field in particular here. I need to know how to create this field so the data will import. Here is an example of two rows of data below, but this data can be edited in the file, to be able to import to the table also:

    2012/12/01,1001,11338,APPLETON COATED LLC,2442664.2
    2012/12/01,1001,11342,ASTEN JOHNSON,48801

    I'm use to Julian dates, but want to be able to use a real date value. Can anyone give me an example or tell me what they would do here? Thank you so much!

    CC

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    The DATE datatype does not have any format, it's just a number. When importing data you specify the format of the string representation of the date, e.g. IMPORT ... MODIFIED BY DATEFORMAT="YYYY/MM/DD"..., to allow DB2 to properly parse those strings.
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Feb 2007
    Posts
    84
    Thank you! That worked and Inserted the rows. Here is what I did:

    db2 import from test.txt of del modified by dateformat=\"YYYY/MM/DD\" messages import.msg insert into proddta.F5541021


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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •