I'm using this soql query in my webservice to query for records created within specific dates and using this as an if condition of the dates are the same

if(fromDate == toDate){
          orderList = [SELECT Id,Name,Account__r.Kennitala__c ,Account__r.Name,Contact__r.Name,phPhoneNumberUsedForService__c,createdDate, RecordType.Name,txServiceOwnerName__c,pl_Status__c,txlOrderInfoFromWeb__c,CreatedBy.Name 
                    from Orders__c where Account__r.ktId__c =:request.str_ktId AND CreatedDate =: fromDate];

fromDate & toDate are values of type Date ands are retrieved from the request (This is a SOAP service). What I'm seeing is that if I have the dates identical, f/x 2014-01-16, then I get nothing returned. But if toDate is set to the date after, like 2014-01-17, then the query returns the records created on 2014-01-16 . I tried doing this in my query tool using instances of DateTime and got the same result.

I'm just wondering, is this expected and do I have to account for this in queries when the from dates and to dates are the same since CreatedDate is DateTime and I'm passing in a Date in the request?

  • a related issue is here: force746.wordpress.com/2013/09/04/… – cropredy Mar 13 '14 at 23:55
  • @crop1645 Wrote my answer before I saw your comment... – Keith C Mar 14 '14 at 0:02
  • Thanks for that, I tried this method but couldn't get it to work. What I did in the end was to create a special case for this type of qery and have the ToDate as FromDate.addDays(1). – akarnid Mar 14 '14 at 13:04

A likely cause of this is implicit conversion of CreatedDate to a Date producing an unexpected result because of the timezone set for your user. According to this Gotcha: convertTimezone() must be used in SOQL Date functions dealing with Datetime blog post by my colleague, the right expression to use for the date part of the "where" is:

DAY_ONLY(convertTimezone(CreatedDate)) = :fromDate
  • This didn't work for me either, what I did was to catch this query and have it return records where toDate=fromdate.addDays(1) – akarnid Mar 18 '14 at 12:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.