Search date generated SQL

Applications Search
#1

I have a simple grid for a table that has a date in it. When I do a search for date, the generated SQL does an “EXTEND(p_date, YEAR TO FRACTION) = ‘2012-08-12’” when searching for 08/12/2012. Is there anyway to tell the system to not do the Extend? The field is just a date, not date/time. SQL type is date in the fields screen. I am using the native Informix driver.

#2

Re: Search date generated SQL

Hello,

Is the search working fine? Or is it displaying wrong records?

regards,
Bernhard Bernsmann

#3

Re: Search date generated SQL

It displays the following error:

Error
Error while accessing the database:
Non-numeric character in datetime or interval.
select count(*) from packer where EXTEND(p_date, YEAR TO FRACTION) = ‘2012-08-12’

p_date is of type date not datetime.

#4

Re: Search date generated SQL

I can get this to work if I change the following lines in the applications generated directory file grid_packer_pesq.class.php.

 if (substr($tp_campo, 0, 4) == "DATE" && in_array(strtolower($this->Ini->nm_tpbanco), $this->Ini->nm_bases_informix))
 {
   $nome   = "EXTEND($nome, YEAR TO FRACTION)";    <-- change from FRACTION to DAY and it works
   $nome_sum = "EXTEND($nome_sum, YEAR TO FRACTION)";  <-- change from FRACTION to DAY and it works
 }

Maybe this needs to be moved to the Bugs section.

#5

Re: Search date generated SQL

Agreed.

I have reported this issue to our bugs team for further testing.

regards,
Bernhard Bernsmann

#6

Re: Search date generated SQL

Hello,

We have fixed this bug and it should be out on our next release.

regards,
Bernhard Bernsmann