By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
432,508 Members | 1,823 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 432,508 IT Pros & Developers. It's quick & easy.

Date parameter as a criteria for data in db2 accessed from msquery returns error

P: n/a
Hi

I am trying to retrieve data from a db2 v8.1 fp2 database via msquery
and I use the add-criteria function to allow users to input a data
parameter.
The value is inputted as YYYY-MM-DD but when I run this, the following
error occurs

[IBM][CLI Driver] CLI0113E SQLSTATE 22007: An invalid datetime format
was detected; that is, an invalid string representation or value was
specified. SQLSTATE=22007" )

Does anyone have any experience with this ?

Thanks
Lyn
Nov 12 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
See what comes out of a query like
select current date from sysibm.sysdummy1
and use the same format.

That would be the quick answer.

PM

"Lyn Duong" <ly**@tablimited.com.au> a écrit dans le message de
news:8d**************************@posting.google.c om...
Hi

I am trying to retrieve data from a db2 v8.1 fp2 database via msquery
and I use the add-criteria function to allow users to input a data
parameter.
The value is inputted as YYYY-MM-DD but when I run this, the following
error occurs

[IBM][CLI Driver] CLI0113E SQLSTATE 22007: An invalid datetime format
was detected; that is, an invalid string representation or value was
specified. SQLSTATE=22007" )

Does anyone have any experience with this ?

Thanks
Lyn

Nov 12 '05 #2

P: n/a
i have checked that the format is YYYY-MM-DD but still get the error
when trying to set the criteria
"PM \(pm3iinc-nospam\)" <PM(pm3iinc-nospam)@sympatico.ca> wrote in message news:<5Y*********************@news20.bellglobal.co m>...
See what comes out of a query like
select current date from sysibm.sysdummy1
and use the same format.

That would be the quick answer.

PM

"Lyn Duong" <ly**@tablimited.com.au> a écrit dans le message de
news:8d**************************@posting.google.c om...
Hi

I am trying to retrieve data from a db2 v8.1 fp2 database via msquery
and I use the add-criteria function to allow users to input a data
parameter.
The value is inputted as YYYY-MM-DD but when I run this, the following
error occurs

[IBM][CLI Driver] CLI0113E SQLSTATE 22007: An invalid datetime format
was detected; that is, an invalid string representation or value was
specified. SQLSTATE=22007" )

Does anyone have any experience with this ?

Thanks
Lyn

Nov 12 '05 #3

P: n/a
Maybe you need quotes.

Depending on the db2 release you have. (db2level command)

You may see the following behavior... no quotes = value taken as int...

Prompt>db2 values date('2003-11-01')
11/01/2003

-- interpreted as 2003 minus 11 minus 1
Prompt>db2 values date(2003-11-01)
06/14/0006

--
Prompt>db2 values (2003-11-01)
1991

-- valid. Internal representation.
Prompt>db2 values date(1991)
06/14/0006

Prompt>db2 values date('11-01-2003')
11/01/2003

db2 v8 fp4
http://publib.boulder.ibm.com/infoce...help/index.jsp
reference
sql
functions
scalar
date

or
http://www-3.ibm.com/cgi-bin/db2www/...ter.d2w/report
advanced search
....

PM

"Lyn Duong" <ly**@tablimited.com.au> a écrit dans le message de
news:8d*************************@posting.google.co m...
i have checked that the format is YYYY-MM-DD but still get the error
when trying to set the criteria
"PM \(pm3iinc-nospam\)" <PM(pm3iinc-nospam)@sympatico.ca> wrote in message

news:<5Y*********************@news20.bellglobal.co m>...
See what comes out of a query like
select current date from sysibm.sysdummy1
and use the same format.

That would be the quick answer.

PM

"Lyn Duong" <ly**@tablimited.com.au> a écrit dans le message de
news:8d**************************@posting.google.c om...
Hi

I am trying to retrieve data from a db2 v8.1 fp2 database via msquery
and I use the add-criteria function to allow users to input a data
parameter.
The value is inputted as YYYY-MM-DD but when I run this, the following
error occurs

[IBM][CLI Driver] CLI0113E SQLSTATE 22007: An invalid datetime format
was detected; that is, an invalid string representation or value was
specified. SQLSTATE=22007" )

Does anyone have any experience with this ?

Thanks
Lyn

Nov 12 '05 #4

P: 1
Did you ever get this resolved. I'm having a similar problem. But I get the SQL0104N error when trying to use the parameter ([param]) in the criteria. It works great for a SQL Server database, but not our version 8 DB2 server.

Michal



Hi

I am trying to retrieve data from a db2 v8.1 fp2 database via msquery
and I use the add-criteria function to allow users to input a data
parameter.
The value is inputted as YYYY-MM-DD but when I run this, the following
error occurs

[IBM][CLI Driver] CLI0113E SQLSTATE 22007: An invalid datetime format
was detected; that is, an invalid string representation or value was
specified. SQLSTATE=22007" )

Does anyone have any experience with this ?

Thanks
Lyn
Jun 15 '06 #5

This discussion thread is closed

Replies have been disabled for this discussion.