SQL query problem with DB2 tables

[djpingpong]

Hi,

I'm working on MS Access connected to db2 tables thru ODBC

Most of the times, I'm workin on Access tables and I create a query like this:

SELECT * FROM tblEarnings WHERE earnEffectiveDate < #5/1/1974#

The query works fine in Access with its local data tables. I get a result set of all the earning records prior to 1974-May-1 .... however, I'm now working on a client project that still uses Access frontend, but connects to DB2 tables... I run the same query and it's giving me a ODBC - call failed error

"The data types of the operands for the operation '<' are not compatible, SQLSTATE = 42818 (#-401)"

Any suggestions?? Need to get this working... it was suppose to be an easy task

cheers

 

[lespaul]

are you trying to use the # date delimiters? DB2 uses ' as the date delimiter. Are you sure the DB2 field is a date field? Or is it a text field formatted to look like a date?

What happens if you try:

SELECT * FROM tblEarnings WHERE earnEffectiveDate < '5/1/1974'

Leslie

Essential for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[SkipVought]

Hi,

As Leslie suggested, be absolutely sure that the DB2 field is a Date.

If the value is a STRING, you will have to convert your date to text in the correct format. I deal with Oracle tables where the "date" is a STRING in the form yyyymmdd.

Skip,

_{When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen!}