You need to sign in to do that
Don't have an account?

SOQL query not returning rows when LastModifiedDate is compared to two dates
Hi All, and thanks for taking the time to look at my post.
I am trying to retrieve a list of contacts modified between two dates (LastModifiedDate >= Date1 AND LastModifiedDate <= Date2). When I query I receive 0 rows. If I take out ONLY the second part (LastModifiedDate <= Date2) I receive many.
Here is my code and the debug log - first with the two dates:
DateTime myDate = DateTime.parse('10/05/2011 00:00 AM'); DateTime myLastDate = DateTime.valueOf('2012-02-7 05:04:25'); System.debug('the first date:' + String.valueOf(myDate) + ' and the last date: ' + String.valueOf(myLastDate)); List<Contact> cList = [select id, FirstName, LastName, LastModifiedDate FROM Contact WHERE Email <> '' AND Email <> null AND LastModifiedDate >= :myDate and lastModifiedDate <= :myLastDate]; system.debug('num cons' + cList.size()); 19:19:14.042 (42468000)|USER_DEBUG|[3]|DEBUG|the first date:2011-10-05 00:00:00 and the last date: 2012-02-07 05:04:25 19:19:14.042 (42478000)|SYSTEM_METHOD_EXIT|[3]|System.debug(ANY) 19:19:14.042 (42682000)|SOQL_EXECUTE_BEGIN|[4]|Aggregations:0|select id, FirstName, LastName, LastModifiedDate FROM Contact WHERE Email <> '' AND Email <> null AND LastModifiedDate >= :myDate and lastModifiedDate <= :myLastDate 19:19:18.055 (4055043000)|SOQL_EXECUTE_END|[4]|Rows:0
and now with one date
DateTime myDate = DateTime.parse('10/05/2011 00:00 AM'); DateTime myLastDate = DateTime.valueOf('2012-02-7 05:04:25'); System.debug('the first date:' + String.valueOf(myDate) + ' and the last date: ' + String.valueOf(myLastDate)); List<Contact> cList = [select id, FirstName, LastName, LastModifiedDate FROM Contact WHERE Email <> '' AND Email <> null AND LastModifiedDate >= :myDate ]; system.debug('num cons' + cList.size()); 19:20:55.039 (39038000)|USER_DEBUG|[3]|DEBUG|the first date:2011-10-05 00:00:00 and the last date: 2012-02-07 05:04:25 19:20:55.039 (39045000)|SYSTEM_METHOD_EXIT|[3]|System.debug(ANY) 19:20:55.039 (39249000)|SOQL_EXECUTE_BEGIN|[4]|Aggregations:0|select id, FirstName, LastName, LastModifiedDate FROM Contact WHERE Email <> '' AND Email <> null AND LastModifiedDate >= :myDate 19:21:06.555 (11555339000)|SOQL_EXECUTE_END|[4]|Rows:21784
Any suggestions are welcome.
This is probably obvious but have you checked what the maximum and minimum LastModifiedDate values are, in the second query? Just to confirm that those 21,000+ records haven't somehow been assigned a future date?
Thanks for taking the time to respond.
Yes, I have checked. There was a batch update done on the first date. When I run it without the second date, and get the lastmodified, the first few thousand have a modifieddate the same - a few seconds later.