Apex Code Development You need to sign in to do that
Don't have an account?
hgolov 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:0and 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:21784Any 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.