Informix DATE type issue across ODBC

Has anybody else come across the following scenario? I am linking to an Informix IDS 7.31.FD7 database through an Informix 3.3 ODBC driver. When I select data for a data range, however, Informix does not recognize the from and to date strings passed across the ODBC driver and returns no data at all, while it works perfectly if I run the exact same SQL statement (copy&paste) in DBACCESS. I have had this in the past as well when Java web apps used ODBC to call SPL procedures. Does anybody know how to get around this problem?

What's the date format you are using? Is it being used in literal operation OR in parameterized query? As per the ODBC standard it got to be Y4MD- format. Whereas if I recall correctly then SQL literal default format is MDY4/ For example, Patameterized: SQLBindParameter( hstmt, 1, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_DATE, tmplen,0, c2, tmplen, c1ind ); strcpy(c2, "2005-11-14"); SQLExecDirect(hstmt, "INSERT INTO test VALUES(?)", SQL_NTS); Literal: SQLExecDirect(hstmt, "INSERT INTO test VALUES('11/13/2005')", SQL_NTS); Could you share your query? What are the database/client locales? -Shesh "THEUNS VAN ONSELEN" <vanons_t@mtn.co.za> Sent by: ids-bounces@iiug.org 14/01/2009 14:16 Please respond to ids@iiug.org To ids@iiug.org cc Subject Informix DATE type issue across ODBC [14519] Has anybody else come across the following scenario? I am linking to an Informix IDS 7.31.FD7 database through an Informix 3.3 ODBC driver. When I select data for a data range, however, Informix does not recognize the from and to date strings passed across the ODBC driver and returns no data at all, while it works perfectly if I run the exact same SQL statement (copy&paste) in DBACCESS. I have had this in the past as well when Java web apps used ODBC to call SPL procedures. Does anybody know how to get around this problem? ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

Here is the application generated query (Business Objects XI) The values are literal user input strings that should get converted to dates on Informix. If I take out the bhd_value17 bit, it returns all the data for the other parameters. bhd_value17 is defined as a DATE on the database table... SELECT informix.bo_hier_dtr.bhh_acc_no FROM informix.bo_hier_hdr,informix.bo_hier_dtr WHERE ( informix.bo_hier_hdr.bhh_hier_id=informix.bo_hier_dtr.bhd_hier_id ) AND ( informix.bo_hier_hdr.bhh_hier_name In ( 'Anglo American PLC' ) AND ( informix.bo_hier_dtr.bhd_rep_type = 'REVENUE' AND informix.bo_hier_dtr.bhd_value17 Between "01052008" And "01072008" ) )

Informix does NOT recognize a string of digits as a date normally. You have to format it according to the client's DBDATE format which defaults to "MMDDY4/" which equates date strings of the form "MM/DD/YYYY", so you should be passing in: informix.bo_hier_dtr.bhd_value17 Between "01/05/2008" And "01/07/2008" Art On Wed, Jan 14, 2009 at 6:30 AM, THEUNS VAN ONSELEN <vanons_t@mtn.co.za>wrote: > Here is the application generated query (Business Objects XI) > > The values are literal user input strings that should get converted to > dates > on Informix. > > If I take out the bhd_value17 bit, it returns all the data for the other > parameters. bhd_value17 is defined as a DATE on the database table... > > SELECT informix.bo_hier_dtr.bhh_acc_no > FROM > informix.bo_hier_hdr,> informix.bo_hier_dtr > WHERE > ( informix.bo_hier_hdr.bhh_hier_id=informix.bo_hier_dtr.bhd_hier_id ) > AND > ( > > informix.bo_hier_hdr.bhh_hier_name In ( 'Anglo American PLC' ) > > AND > > ( > > informix.bo_hier_dtr.bhd_rep_type = 'REVENUE' > > AND > > informix.bo_hier_dtr.bhd_value17 Between "01052008" And "01072008" > > ) > ) > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Art S. Kagel Oninit (www.oninit.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Oninit, the IIUG, nor any other organization with which I am associated either explicitly or implicitly. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves.

Tried that as well, no luck... informix.bo_hier_dtr.bhd_value17 BETWEEN '01/05/2008' AND '01/08/2008'