Results 1 to 15 of 15

Thread: Insert Error

  1. #1
    Join Date
    Mar 2004
    Posts
    7

    Unanswered: Insert Error

    Hai,
    While Inserting Datetime values into a table i'm getting an error
    as "The Syntax of String reprensentation of datetime is incorrect SQLSTATE 220077"
    i'm giving the value as ('1980-01-01')
    can someone help me in resolving this

    Thanks
    Durai

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    What is the column type? Please post the exact SQL statement.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650

    Re: Insert Error

    What is the type of the column ?

    Post the DDL for table and the INSERT Statement

    Cheers
    Sathyaram

    Originally posted by skdmdurai
    Hai,
    While Inserting Datetime values into a table i'm getting an error
    as "The Syntax of String reprensentation of datetime is incorrect SQLSTATE 220077"
    i'm giving the value as ('1980-01-01')
    can someone help me in resolving this

    Thanks
    Durai
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  4. #4
    Join Date
    Mar 2004
    Posts
    7
    Hi,
    The Field is Date and length of 10.

    Durai

  5. #5
    Join Date
    Mar 2004
    Posts
    7
    Hi,
    The Table is already created ,now we want to insert values into it.
    i'm giving the following insert statement

    Insert into a_tbl (emplid,dob)values('1',1980-02-21')

    pls help me in solving this issue

    Thanks,
    Durai

  6. #6
    Join Date
    Mar 2004
    Location
    Toronto, ON, Canada
    Posts
    513
    Originally posted by skdmdurai
    Hi,
    The Table is already created ,now we want to insert values into it.
    i'm giving the following insert statement

    Insert into a_tbl (emplid,dob)values('1',1980-02-21')

    pls help me in solving this issue

    Thanks,
    Durai
    Send the output of "describe" for the table... and you're missing an openning quote before 1980.

    If it's a TIMESTAMP column, you have to use a full timestamp, to see the format do a VALUES (CURRENT TIMESTAMP).
    --
    Jonathan Petruk
    DB2 Database Consultant

  7. #7
    Join Date
    Mar 2004
    Posts
    7
    Missing of the quote was a typing mistake. what is the query to describe a table


    Durai

  8. #8
    Join Date
    Mar 2004
    Location
    Toronto, ON, Canada
    Posts
    513
    Originally posted by skdmdurai
    Missing of the quote was a typing mistake. what is the query to describe a table


    Durai
    describe table <tabname>
    --
    Jonathan Petruk
    DB2 Database Consultant

  9. #9
    Join Date
    Mar 2004
    Posts
    7
    Originally posted by J Petruk
    describe table <tabname>
    Hi,
    find the desc of my table

    Column Type Type
    name schema name Length Scale Nulls
    ------------------------------ --------- ------------------ -------- ----- ------
    EMPLID SYSIBM VARGRAPHIC 11 0 No
    SEX SYSIBM VARGRAPHIC 1 0 No
    ADDRESS1 SYSIBM VARGRAPHIC 55 0 No
    ADDRESS2 SYSIBM VARGRAPHIC 55 0 No
    CITY SYSIBM VARGRAPHIC 30 0 No
    STATE SYSIBM VARGRAPHIC 6 0 No
    POSTAL SYSIBM VARGRAPHIC 12 0 No
    PHONE SYSIBM VARGRAPHIC 24 0 No
    DEPEND_BIRTHDATE SYSIBM DATE 4 0 Yes

  10. #10
    Join Date
    Mar 2004
    Location
    Toronto, ON, Canada
    Posts
    513
    I honestly can't see anything wrong with your INSERT... this is the exact SQL and table definition?

    If you're doing this from the command line, can you put the INSERT in quotes?

    ie.
    db2 "insert into .... values ... "
    --
    Jonathan Petruk
    DB2 Database Consultant

  11. #11
    Join Date
    Mar 2004
    Posts
    7
    Hi,
    The table is correct.i'm using Quest central for running the db2 statements. the format of the date is correct? what i'm using? i.e 'yyyy-mm-dd'
    bcos while running it gives error of sqlstate:220077
    i also tried with giving as '1981-01-20.00.00.00'.this is also not taking
    kindly help me in solving it

    Durai

  12. #12
    Join Date
    Mar 2004
    Location
    Toronto, ON, Canada
    Posts
    513
    Originally posted by skdmdurai
    Hi,
    The table is correct.i'm using Quest central for running the db2 statements. the format of the date is correct? what i'm using? i.e 'yyyy-mm-dd'
    bcos while running it gives error of sqlstate:220077
    i also tried with giving as '1981-01-20.00.00.00'.this is also not taking
    kindly help me in solving it

    Durai
    Ahhh, ODBC... that makes a difference.

    TIMESTAMP is this:
    yyyy-mm-dd hh:mm:ss.nnnnnn

    I thought date was just yyyy-mm-dd, but maybe not.
    --
    Jonathan Petruk
    DB2 Database Consultant

  13. #13
    Join Date
    Mar 2004
    Posts
    7
    i will have to give may values like '1998-08-20 20:00:00.000000' ?
    it is correct

  14. #14
    Join Date
    Mar 2004
    Location
    Toronto, ON, Canada
    Posts
    513
    Originally posted by skdmdurai
    i will have to give may values like '1998-08-20 20:00:00.000000' ?
    it is correct
    Yes, but I think that's only for timestamp.

    What do you get back if you do this? That should tell you what format it's using for DATE:
    select current date from sysibm.sysdummy1
    --
    Jonathan Petruk
    DB2 Database Consultant

  15. #15
    Join Date
    Mar 2004
    Location
    Toronto, ON, Canada
    Posts
    513
    BTW, I'm not an ODBC expert by any stretch, but apparantly it depends on the BIND option that was used for DATETIME.
    --
    Jonathan Petruk
    DB2 Database Consultant

Posting Permissions

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