Query Cases based on Last Modified DateTime and Last Modified By User

Question

So here's the scenario. A user (lets call him John Doe) un-deleted the Cases records from the Recycle Bin and now they are back in the system along with current Case records. I need to delete them back.

Now those cases have last modified datetime as the time when they were un-deleted, so now I have my first filter criteria, second being where the last modified by user was John Doe.

I can run a report and get those Case Ids. However I need to run a query on the dev console to check some other stuff as well.

The suspected last modified date time we have for those cases is 06/29/2017 4:15 PM - 06/29/2017 5:00 PM

So in my dev console I want to do a query that looks like:

select Id, Subject from Case where LastModifiedDate >= 2017-06-29T16:15 and LastModifiedDate <= 2017-06-29T17:00 and LastModifiedBy = 'User Id for John Doe'

However, the LastModifiedDate and LastModifiedBy fields are not recognized and it is giving me error saying, "no such field on Case"

How do I make this query work? Do I need to query CaseHistory instead of Case? And what field name do I use for last modifed datetime and last modifed by user?

Answer 1

Two things that are incorrect in your query:

• As glls mentioned, the date format must include the second and timezone parts
• You cannot directly use LastModifiedBy, you should use LastModifiedBy.Name to filter on the name of the user or LastModifiedById (convenience field available on all SObjects, the same as LastModifiedBy.Id)

The following query parses for me:

select Id, Subject from Case where LastModifiedDate >= 2017-06-29T16:15:00Z and LastModifiedDate <= 2017-06-29T17:00:00Z and LastModifiedBy.Name = 'User Id for John Doe'

Answer 2

I believe your date formatting is incorrect, so your current query should be returning an unkown error while parsing the query.

your date formatting should be as follows:

2017-06-29T17:00:00Z

And try adding parenthesis for your expression after WHERE