Query returning different results from coldfusion

[vksrinu]

Hi,

I have below query which is returning the previous day results also. when i am executing the same query in query tool its returning the only today's orders. recently we have migrated the servers and coldfusion to newer versions, after the migration we are facing this issue.

below the query:

select eoo_dist_organization, eoo_dist_po_no, eoo_insertdatetime, eoo_order_date, eoo_is_acknowledged from edi_orders

where eoo_insertdatetime >= '01-Mar-2012 00:00:00' AND

eoo_insertdatetime <= '01-Mar-2012 23:59:59'

order by eoo_insertdatetime desc

Normally this query should return only todays' orders but its returning yesterday orders also. As per my observation this query works till 6:30PM EST(returns only today's orders) after 6:30 PM EST it also returns the yesterday's orders also. Does any one faced this issue. what could be the issue.

[insuractive]

Check the date/time settings on your web & database servers.  If they are not synced or are in a (drastically) different time zone, that could explain what you are seeing.

[vksrinu]

the server timings are correct. I have verified.  It looks the issue is happening with the JVM may be. the server is having the timezone as EDT but where as the Coldfusion(java jvm) is taking the timezone as GMT. so if i am running a query for today results its pulling the data from yesterday 8:30PM to today 8:30PM.

[Dan_Bracuk]

What happens when you run something like this, or whatever is appropriate for the db type you are using?

select sysdate dbdate, #now()# cfdate

from dual

[BKBK]

where eoo_insertdatetime >= '01-Mar-2012 00:00:00' AND

eoo_insertdatetime <= '01-Mar-2012 23:59:59'

The enigine might assume you're attempting to compare strings! Drop the quotes and use datetime objects. What about something like

where eoo_insertdatetime >= #createdatetime(2012,3,1,0,0,0)# AND

eoo_insertdatetime <= #createdatetime(2012,3,1,23,59,59)#

[vksrinu]

I have open an issue with the Adobe support team and they are saying that the issue could be with the Ingres drivers. It looks when the query is executing the drivers are taking the GMT timings instead of EDT timings. there is a difference of 4:30 mins timings, the orders between these timings are missing from the query. cant able to find any solution.