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.

[#1]

any advice is appreciated.  thanks!

[#2]

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

[#3]

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.

[#4 · Accepted 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:

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

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

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

[#5]

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")

[#6]

This worked!  Thanks PMroz.

[#7]

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

[#8]

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