0

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?

3
  • a related issue is here: force746.wordpress.com/2013/09/04/…
    – cropredy
    Mar 13, 2014 at 23:55
  • @crop1645 Wrote my answer before I saw your comment...
    – Keith C
    Mar 14, 2014 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, 2014 at 13:04

1 Answer 1

2

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
1
  • 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, 2014 at 12:49

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .