Date in Batch apex query

Question

I am writing an Batch apex where I need to query the records which are created today(System date). I created one field name Created_Date_Formatted__c which just gives me only date from CreateDate field. In my start(Database.BatchableContext BC) method, I am passing my query as string

query ='SELECT id,name FROM sObject__c WHERE Created_Date_Formatted__c = '+ String.valueOf(System.now());

I have records which has created_Date_format__c as today, When I am querying the records in query editor without any where clause it gives me all the records

But when I copy the date(red marked) and put the where clauses it does not work

I have tried putting date in different format but it doesn't work. I would really appreciate if anyone can help me. Please let me know if anything else is also required from my side. I will update the thread. Thanks

Answer 1

Don't use a formula like this. You'll end up needing a full table scan every time you run this query, which will easily time out on larger tables. Instead, you can literally just say "today" in your query:

return Database.getQueryLocator([
  SELECT Id FROM SObject__c WHERE CreatedDate = TODAY
]);

"TODAY" automatically selects all records created from midnight local time (according to the user's time zone). If you want records from the last 24 hours, you can calculate that, too:

DateTime twentyFourHoursAgo = System.now().addHours(-24);
return Database.getQueryLocator([
  SELECT Id FROM SObject__c WHERE CreatedDate >= :twentyFourHoursAgo
]);

When you absolutely need to filter on a formula that returns text, you still need to quote the value:

SELECT Created_Date_Formatted__c FROM SObject__c 
WHERE Created_Date_Formatted__c = '2017-08-09'

Answer 2

Have you tried this using workbench ? I just checked at my end its works fine. Please make sure in your case, Created_Date_Formatted__c is of Date data type.

Answer 3

Try with this work if your Created_Date_Formatted__c field is a Date Type

query ='SELECT id,name FROM sObject__c WHERE Created_Date_Formatted__c = today'

You can also use Standard field to get result by creadteDate. Date and time when this record was created. CreatedDate fields have Defaulted on create and Filter access.

query ='SELECT id,name FROM sObject__c WHERE DAY_ONLY(CreatedDate) = 2005-10-08

or

 query ='SELECT id,name FROM sObject__c WHERE DAY_ONLY(CreatedDate) = today

To filter a SOQL query on a datetime field with a day value, use the DAY_ONLY SOQL operator.