Using Date in SOQL to get only records created on a certain date

Question

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?

Answer 1

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