Results 1 to 8 of 8
  1. #1
    Join Date
    Oct 2010
    Posts
    27

    Unanswered: SQL DateTime Representation Error

    I need some help from some DB2 gurus...I know a little about ksh scripting and even less about DB2.

    I am running a ksh script which will do SQL query to a TSM v6.2 server, which runs on DB2. We used to run this script all the time for previous verisions of TSM which ran a proprietory SQL db. TSM v6 switched to DB2.

    I get this error in my output file.

    ANR0162W Supplemental database diagnostic information: -1:22007:-180 ([IBM][CLI Driver][DB2/AIX64] SQL0180N The syntax of the string representation of a datetime value is incorrect. SQLSTATE=22007

    This is the original code for dateTime:

    dateTime () {
    timeStamp=$(date +'%Y-%m-%d %H:%M:%S')
    theDate=$(date +'%m-%d-%Y')
    theTime=$(date +'%H:%M:%S')
    return
    }

    I know this needs to be changed to DB2 format, so I've tried coding this:

    dateTime () {
    timeStamp=$(date +'%Y-%m-%d-00.00.00.000000')
    theDate=$(date +'%m-%d-%Y')
    theTime=$(date +'%H:%M:%S')
    return
    }

    and I get the same error. Do I need to update theDate and theTime fields? Am I using the wrong format? Any help will be greatly appreciated.

    Thanks!

  2. #2
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963
    Why
    date +'%Y-%m-%d %H:%M:%S'
    Maybe: date +'%H:%M:%S + .000000'
    Or '%Y-%m-%d %H:%M:%S + .000000' ?

    Lenny
    Last edited by Lenny77; 10-07-10 at 15:27.

  3. #3
    Join Date
    Oct 2010
    Posts
    27
    Lenny...thanks, but no luck. I get same error.

  4. #4
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963
    Use:

    Timestamp = Timestamp(date, time)

    Lenny

  5. #5
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by booman55 View Post
    dateTime () {
    timeStamp=$(date +'%Y-%m-%d-00.00.00.000000')
    theDate=$(date +'%m-%d-%Y')
    theTime=$(date +'%H:%M:%S')
    return
    }
    I don't see how that's related to DB2. May be it would make sense for you to print the actual query text before it is executed - it will let you check what may be wrong with it.

  6. #6
    Join Date
    Oct 2010
    Posts
    27
    Quote Originally Posted by n_i View Post
    I don't see how that's related to DB2. May be it would make sense for you to print the actual query text before it is executed - it will let you check what may be wrong with it.
    DB2 comes into play because DB2 has a different date representation for timestamp.

    I opened another thread in the SQL forum which was moved here which has the entire script if you want to look at that.

    Thanks for the help

  7. #7
    Join Date
    Oct 2010
    Posts
    27
    Any ideas? I'm really in need of some assistance.

    Thanks.

  8. #8
    Join Date
    Oct 2010
    Posts
    14

    the date format is incorrect..

    You need to change this:

    theDate=$(date +'%m-%d-%Y')

    To This:

    theDate=$(date +'%Y-%m-%d')

    DB2 usually accepts yyyy-mm-dd or dd/mm/yyyy or mm/dd/yyyy - i don't think i've seen the above. Here's a replication of the error:

    C:\Users\Andy>db2 select * from sysibm.sysdummy1 where current date = '10-09-2010' with ur
    SQL0180N The syntax of the string representation of a datetime value is
    incorrect. SQLSTATE=22007


    Hope this helps

Posting Permissions

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