Subscribe Bookmark RSS Feed

I'm extracting data from an Informix DB using an ODBC driver but the date filter (JMP query builder) throws an error. see screenshot attached.

ronaldochoa

Community Trekker

Joined:

Apr 28, 2016

any advice is appreciated.  thanks!

1 ACCEPTED SOLUTION

Accepted Solutions
Solution

Okay, in researching this, it seems that the date literal format that Informix recognizes is affected by Informix options like DBDATE.  See this page for example. That article suggests that DBDATE can be set on the client side, perhaps in the ODBC driver setup.  However, it looks like all of the formats Informix likes have separators between the date parts.

Here is what I would suggest as a workaround:

Set up your filter the way you did originally:

12725_custfilter1.png

Then, use the red triangle menu in the filter to change the filter type to "Custom Expression":

12726_custfilter2.png

That will change the filter to just be an entry field:

12728_custfilter3.png

Then, try changing '20130216' (or whatever your date is) to '2013-02-16', and then click outside of the Custom Expression box to let it process.  See if you still get the error.  If so, try '2013/02/16'.  Failing that, you may need to ask the adminitrators of your Informix database how your installation of Informix is expecting date literals to be formatted (a.k.a., what value does DBDATE have).  Also, I don't have the Informix ODBC driver installed at the moment, but you could look there and see if there is a way to configure DBDATE in the driver such that the default date format that Query Builder is using would work for you.

HTH,

Eric

7 REPLIES
erichill

Staff

Joined:

Oct 1, 2013

Hey, ronaldochoa,

Could you dismiss the error message and then select the SQL tab in the bottom middle section of Query Builder and attach a screenshot of that?

There is an SQL standard for how date-time values are represented as literals in SQL statements, but many database vendors deviate from that standard.  Query Builder's SQL code generation is aware of many of those deviations, but not all, so I suspect Query Builder is not formatting the date-time literal in a way that Informix can interpret for your filter on row_date.

Thanks,

Eric

ronaldochoa

Community Trekker

Joined:

Apr 28, 2016

Thanks Eric. See screenshot of the SQL created.  I tried to use date time functions like MDY() or CAST but the Query Builder rejects them and removes the filter altogether, and the tool won't let me edit the SQL manually either.

12720_InformixDateError2.JPG

Solution

Okay, in researching this, it seems that the date literal format that Informix recognizes is affected by Informix options like DBDATE.  See this page for example. That article suggests that DBDATE can be set on the client side, perhaps in the ODBC driver setup.  However, it looks like all of the formats Informix likes have separators between the date parts.

Here is what I would suggest as a workaround:

Set up your filter the way you did originally:

12725_custfilter1.png

Then, use the red triangle menu in the filter to change the filter type to "Custom Expression":

12726_custfilter2.png

That will change the filter to just be an entry field:

12728_custfilter3.png

Then, try changing '20130216' (or whatever your date is) to '2013-02-16', and then click outside of the Custom Expression box to let it process.  See if you still get the error.  If so, try '2013/02/16'.  Failing that, you may need to ask the adminitrators of your Informix database how your installation of Informix is expecting date literals to be formatted (a.k.a., what value does DBDATE have).  Also, I don't have the Informix ODBC driver installed at the moment, but you could look there and see if there is a way to configure DBDATE in the driver such that the default date format that Query Builder is using would work for you.

HTH,

Eric

pmroz

Super User

Joined:

Jun 23, 2011

I would force the issue by using to_date.  That way you won't have to rely on knowing what the default date mask is in a database.  I don't have Informix handy so can't test this, but it's from the internet, so it must be true

to_date('12/12/2013',"%d/%m/%Y")

ronaldochoa

Community Trekker

Joined:

Apr 28, 2016

This worked!  Thanks PMroz.

ronaldochoa

Community Trekker

Joined:

Apr 28, 2016

That worked Eric, thanks!  The mm/dd/YYYY format worked.  The solution of PMroz also worked.

erichill

Staff

Joined:

Oct 1, 2013

Great!  That Informix page did say that the default value of DBDATE is 'MDY4/', so if 'mm/dd/YYYY' format worked, your Informix installation is apparently using the default value of DBDATE.

Eric