cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Try the Materials Informatics Toolkit, which is designed to easily handle SMILES data. This and other helpful add-ins are available in the JMP® Marketplace
Choose Language Hide Translation Bar
View Original Published Thread

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
Level II

any advice is appreciated.  thanks!

1 ACCEPTED SOLUTION

Accepted Solutions
Eric_Hill
Staff


Re: 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.

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

View solution in original post

7 REPLIES 7
Eric_Hill
Staff


Re: 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.

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
Level II


Re: 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.

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

Eric_Hill
Staff


Re: 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.

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


Re: 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.

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
Level II


Re: 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.

This worked!  Thanks PMroz.

ronaldochoa
Level II


Re: 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.

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

Eric_Hill
Staff


Re: 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.

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