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!

Why Maybe: date +'%H:%M:%S + .000000'
Or '%Y-%m-%d %H:%M:%S + .000000' ?

Lenny

Lenny...thanks, but no luck. I get same error.

Use:

Timestamp = Timestamp(date, time)

Lenny

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

Any ideas? I'm really in need of some assistance.

Thanks.

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