Oracle OCI: Query with Date field -

client compiled oci: 10.2.0.4.0
server: oracle9i enterprise edition release 9.2.0.4.0

the problematic query is:

select codigo log tel = :telnumber , date_proc = '05-jul-08' 

table description:

sql>describe log;  tel not null varchar2(15) codigo not null varchar2(20) date_proc not null date 

as simple might look, when executed directly on server sqlplus, returns result, when executed app uses oci, query returns oci_no_data always. in beginning, date value placeholder, found out giving literal '05-jul-08' didn't work. have tried following:

• i've tried basics: querying db client work. it's 1 gives me trouble

• the following work:

  select codigo log tel = :telnumber 

• executing alter session set nls_date_format="dd-mm-yyyy"; before query in both server , client. same result: server returns data, client oci_no_data

• tried changing date_proc format, combining use of to_date(). same result.
• searched, searched, searched. no answer

i'm bit desperate find answer, appreciate , can provide many further details needed. thanks.

--- further info ---

update log set date_proc = to_date('20080705162918', 'yyyymmddhh24miss') codigo='bancoone'; 

i have tried different combinations using trunc() , "alter session set nls_date_format"... , get:

select codigo log tel = 11223344 , date_proc = to_date('20080705162918', 'yyyymmddhh24miss'); 

in server: returns: "bancoone" (good value)
in oci app: returns oci_no_data

select codigo log tel = 11223344 , trunc(date_proc) = to_date('20080705', 'yyyymmdd'); 

in server: returns: "bancoone"
in oci app: returns "bancoone"

so point is, why oci app giving different results if both accessing same db server?

also, clarify purpose of oci app: has query configured user. idea user adapt query desired fit date field present in destination db, that's why should not include "alter session set nls_date_format" statements in code, not know date format. way want provide flexibility user, , don't rely on specific date formats. make sense? suggestions?

your column date_proc date, should always compare date , never rely on implicit data conversion.

try this:

select codigo log tel = :telnumber , date_proc = date '2008-07-05' 

or this:

select codigo    log   tel = :telnumber     , date_proc = to_date('05-jul-08', 'dd-mon-rr') 

if can, refrain using date format in code uses letters months (because code will fail when change default language) , 2 chars years (ambiguity of century). use 'yyyy/mm/rr' because format sorted original date.