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.
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.
What happens when you run something like this, or whatever is appropriate for the db type you are using?
select sysdate dbdate, #now()# cfdate
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)#
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.