Skip navigation
Currently Being Moderated

Query returning different results from coldfusion

Mar 5, 2012 5:45 AM

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.

 
Replies
  • Currently Being Moderated
    Mar 5, 2012 7:55 AM   in reply to Srinivas VK

    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.

     
    |
    Mark as:
  • Currently Being Moderated
    Apr 9, 2012 10:29 AM   in reply to Srinivas VK

    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

     
    |
    Mark as:
  • Currently Being Moderated
    Apr 12, 2012 6:30 AM   in reply to Srinivas VK

    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)#

     
    |
    Mark as:

More Like This

  • Retrieving data ...

Bookmarked By (0)

Answers + Points = Status

  • 10 points awarded for Correct Answers
  • 5 points awarded for Helpful Answers
  • 10,000+ points
  • 1,001-10,000 points
  • 501-1,000 points
  • 5-500 points