Skip to main content
vksrinu
vksrinu
Inspiring
March 5, 2012
Question

Query returning different results from coldfusion

  • March 5, 2012
  • 2 replies
  • 1833 views

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.

This topic has been closed for replies.

2 replies

BKBK
Community Expert
BKBKCommunity Expert
Community Expert
April 12, 2012

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
vksrinuAuthor
Inspiring
May 21, 2012

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.

I
insuractive
Inspiring
March 5, 2012

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
vksrinuAuthor
Inspiring
April 9, 2012

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.

D
Dan_Bracuk
Inspiring
April 9, 2012

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

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices