0

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 enter image description here

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

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

  • Have you tried putting quotes around the date? – Nick Cook Aug 10 '17 at 4:34
  • yes it throws error. Created_Date_Formatted__c FROM sobject__c WHERE Created_Date_Formatted__c = '2017-08-09' ^ ERROR at Row:1:Column:65 value of filter criterion for field 'Created_Date_Formatted__c' must be of type date and should not be enclosed in quotes – Sagar Aug 10 '17 at 4:36
  • why are you creating text fields for date, it should be date field – Santanu Boral Aug 10 '17 at 4:39
  • Created_Date_Formatted__c is Date type. – Sagar Aug 10 '17 at 4:46
4

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.getQuerySelector([
  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.getQuerySelector([
  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'
0

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.

enter image description here

0

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.

  • Created_Date_Formatted__c is type Date type only. – Sagar Aug 10 '17 at 4:47
  • Then above soql should work for you. – NITHESH K Aug 10 '17 at 4:56
  • Created_Date_Formatted__c is custom date field you created ? – NITHESH K Aug 10 '17 at 4:57
  • Thanks for your reply. query ='SELECT id,name FROM sObject__c WHERE Created_Date_Formatted__c = today' worked. – Sagar Aug 10 '17 at 5:40
  • Your welcome.... – NITHESH K Aug 10 '17 at 5:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.