Informix and DateTimes and Syntax Errors, Oh my!

Kenneth Brown

<p style="font-size:13.6000003814697px;color:rgb(0,0,0);font-family:Arial, 'Liberation Sans', 'DejaVu Sans', sans-serif;">I'm running into a wall of syntax errors trying to use a Date parameter in a query against an Informix (11.50) database. I'm using this query:</p>
<p style="margin-left:40px;">select<br>
distinct SOMEVARIABLE<br>
from<br>
SOMETABLE<br>
where SOMEDATETIME > datetime (?) year to minute<br>
and SOMEDATETIME < datetime (2014-10-30) year to day</p>
<p> </p>
<p style="font-size:13.6000003814697px;color:rgb(0,0,0);font-family:Arial, 'Liberation Sans', 'DejaVu Sans', sans-serif;">The query parameter type is set to Date and the report paramter is set to DateTime, formatted as a Short Date.</p>
<p style="font-size:13.6000003814697px;color:rgb(0,0,0);font-family:Arial, 'Liberation Sans', 'DejaVu Sans', sans-serif;">The error I get is: java.sql.SQLException: Non-numeric character in datetime or interval.</p>
<p style="font-size:13.6000003814697px;color:rgb(0,0,0);font-family:Arial, 'Liberation Sans', 'DejaVu Sans', sans-serif;">If I comment out the where clause, I can get BIRT to show me the value it's interpolating in the error: <strong>2014-10-27 00:00</strong>.</p>
<p style="font-size:13.6000003814697px;color:rgb(0,0,0);font-family:Arial, 'Liberation Sans', 'DejaVu Sans', sans-serif;">As far as I can tell, this is the proper formatting for an Informix datetime literal. I'm beginning to lose what's left of my mind here...</p>

Kenneth Brown

<p>I also screamed for help on StackOverflow and got some info.</p>
<p> </p>
<div>It looks like this may be a combination of my inexperience with Informix and my environment. I'm formulating testing my queries with SQuirrel, using the same JDBC config as BIRT. In SQuirrel, I'm getting syntax errors if I don't use the long datetime literal format *datetime (YYYY-MM-DD) year to day*.</div>
<div> </div>
<div>Cutting the literal down to just *YYYY-MM-DD* in the query within BIRT worked just fine.</div>
<div> </div>
<div>I'm guessing this has something to do with how SQuirrel is setting up it's session with the database, but sufficient to the report is the evil thereof.</div>