Copy link to clipboard
Copied
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.
Copy link to clipboard
Copied
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.
Copy link to clipboard
Copied
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.
Copy link to clipboard
Copied
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
Copy link to clipboard
Copied
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)#
Copy link to clipboard
Copied
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.