+ Start a Discussion
brozinickrbrozinickr 

Error: System.QueryException: value of filter criterion for field must be of type date...

Hello,

 

I was wondering if someone would be able to help with me fix this error that I am getting when I am testing my batch apex class.  The purpose of the class is to find opportunities that have a Last_AS_Verbal_Contact__c greater than 7 Days.  Last_AS_Verbal_Contact__c is a date field formula that is based off a field of the same name on the Account.  The premise is for it to find these opportunities and then switch them to closed lost.

 

Here is the full error that I am receiving:

 

System.QueryException: value of filter criterion for field 'Last_AS_Verbal_Contact__c' must be of type date and should not be enclosed in quotes

 

global class OpprtunityBatchUpdate implements Database.Batchable<SObject> {
global Database.QueryLocator start(Database.BatchableContext BC){

    Datetime dateTm = Datetime.now().addDays(-7);

String query = 'Select o.id, o.StageName, o.Last_AS_Verbal_Contact__c from Opportunity o where '
+ 'o.Last_AS_Verbal_Contact__c <= ' +dateTm.format('yyyy-MM-dd\'T\'hh:mm:ss\'z\'') + 'and '
+ 'o.StageName in (\'Discovery\',\'Contract\',\'Quote\') ';

return Database.getQuerylocator(query);
}

global void execute(Database.BatchableContext BC, List<sObject> scope){

    List<Opportunity> opps = new List<Opportunity>();
    system.debug('opportunity size '+scope.size());

        for(sObject s : scope){
        
            Opportunity o = (Opportunity)s;
            system.debug('opportunity name '+o.id);
            o.StageName = 'Closed Lost';
            opps.add(o);
        }
        update opps;
}

global void finish(Database.BatchableContext BC){

// Get the ID of the AsyncApexJob representing this batch job
// from Database.BatchableContext.
// Query the AsyncApexJob object to retrieve the current job's information.

AsyncApexJob a = [SELECT Id, Status, NumberOfErrors, JobItemsProcessed,
TotalJobItems, CreatedBy.Email
FROM AsyncApexJob WHERE Id =
:BC.getJobId()];

// Send an email to the Apex job's submitter notifying of job completion.
Messaging.SingleEmailMessage mail = new Messaging.SingleEmailMessage();
String[] toAddresses = new String[] {a.CreatedBy.Email};
mail.setToAddresses(toAddresses);
mail.setSubject('Apex Sharing Recalculation ' + a.Status);
mail.setPlainTextBody
('The batch Apex job processed ' + a.TotalJobItems +
' batches with '+ a.NumberOfErrors + ' failures.');
Messaging.sendEmail(new Messaging.SingleEmailMessage[] { mail });

}
}

 

Here's my schedulable class with the test method I am using to test:

 

global class ScheduleOpportunityBatch implements Schedulable {

    global void execute(SchedulableContext sc){
    database.executeBatch(new OpprtunityBatchUpdate());
    }
    
    public static testMethod void testScheduleOpprtunityBatch(){
        Test.startTest();
        ScheduleOpportunityBatch objScheduleOpportunityBatch = new ScheduleOpportunityBatch();
        DateTime dtt=DateTime.now().addMinutes(1);    
        String dt=String.valueOf(dtt.second())+' '+String.valueof(dtt.minute())+' '+String.valueof(dtt.hour())+' '+String.Valueof(dtt.day())+' '+String.Valueof(dtt.month()) +' ? '+String.valueof(dtt.year());
        String JobId = System.Schedule('ScheduleOpportunityBatch',dt,objScheduleOpportunityBatch);                        
        Test.stopTest();
    }
    
}

 

 

 

 

Suresh RaghuramSuresh Raghuram

Make the following changes

 

String query = 'Select o.id, o.StageName, o.Last_AS_Verbal_Contact__c from Opportunity o where 
 o.Last_AS_Verbal_Contact__c <= \' +dateTm.format('yyyy-MM-dd\'T\'hh:mm:ss\'z\'')\' + and 
+ o.StageName =:\'Discovery\' OR StageName=:\'Contract\' OR StageNem=:\'Quote\' ';

 I think this should work, check with the escaping single quotes and don't bring the fields under quotes double time because already you are passing them as a string under quotes iam refering the (starting and ending  quotes).

 

Check with the datatypes . passing and receiving fields were of same data type of not.

Pink part try yours, if it does not work go with mine. Let me know if yours work I never tried it.

 

If this answers your question make this as a solution. 

 

brozinickrbrozinickr
I am getting this error now:

Error: Compile Error: line 7:155 no viable alternative at character '\' at line 7 column 155

The part it's failing in the string it between the two d's in yyy-MM-dd formating portion.

String query = 'Select o.id, o.StageName, o.Last_AS_Verbal_Contact__c from Opportunity o where o.Last_AS_Verbal_Contact__c <= \' +dateTm.format('yyyy-MM-dd\'T\'hh:mm:ss\'z\'')\' + and + o.StageName =:\'Discovery\' OR StageName=:\'Contract\' OR StageNem=:\'Quote\' ';

Suresh RaghuramSuresh Raghuram
where the 155 comes.

See you should remember one thing when you are passing query as string if you use quotes , you should escape it by using string escaping symbols, if you search online you will find it
Suresh RaghuramSuresh Raghuram

7 Days.  Last_AS_Verbal_Contact__c is a date field formula 

 

you mention as date field, but you are passing datetime, make sure with that also

brozinickrbrozinickr

Aha!  Changng the date type worked.  Thanks for your help!  Do you know anything about test coverage for batch apex as well?  I'm getting this error in my test code coverage

 

System.UnexpectedException: No more than one executeBatch can be called
from within a testmethod.  Please make sure the iterable returned from
your start method matches the batch size, resulting in one executeBatch
invocation.

 

@isTest (SeeAllData=True)
private class TestUpdateOppBatch{
public static testMethod void OpprtunityBatchUpdate(){
    
        date myDate = System.today();
        date newDate = mydate.addDays(-7);
        
        Account a = new Account();
        a.Name = 'Test Name';
        a.Last_AS_Verbal_Contact__c = newDate;
        insert a;

        List<Opportunity> opps = new List<Opportunity>();
       
           for(integer i = 0; i < 200; i++ ) {
               
               Opportunity opp = new Opportunity( Name='Test Opp' + i,
                                                  CloseDate = System.Today(),
                                                  AccountId = a.id,
                                                  Type = 'New Ad Sales',
                                                  LeadSource = 'Marketing Mailer',
                                                  StageName = 'Discovery' );
                                                  opps.add(opp);
           }

        insert opps;
        
        Test.startTest();           
        OpprtunityBatchUpdate obu = new OpprtunityBatchUpdate();
        ID batch_processId = Database.executeBatch(obu,200);
        Test.stopTest();
    }
    }

 

Suresh RaghuramSuresh Raghuram

Try this place b/w  the start & stop method s

OpprtunityBatchUpdate batchJob= new OpprtunityBatchUpdate();

Database.BatchableContext Bc;

batchJob.start(BC);

batchJob.execute(BC,opp);

batchJob.finish(Bc);

 

 

If this answers your question make this as solution. It will be helpfull to others.

Suresh RaghuramSuresh Raghuram
Some time it becomes hard for it to find the batch size, so the way i said will be help full dont forget to mark it as solution
brozinickrbrozinickr

Thanks for your help Suree.

 

I changed the code as you suggested, but I am getting this error now:

 

System.LimitException: Too many query locator rows: 10001

 

Class.OpprtunityBatchUpdate.start: line 10, column 1
Class.TestUpdateOppBatch.OpprtunityBatchUpdate: line 31, column 1

 

 

 

@isTest (SeeAllData=True)
private class TestUpdateOppBatch{
public static testMethod void OpprtunityBatchUpdate(){
    
        date myDate = System.today();
        date newDate = mydate.addDays(-7);
        
        Account a = new Account();
        a.Name = 'Test Name';
        a.Last_AS_Verbal_Contact__c = newDate;
        insert a;

        List<Opportunity> opps = new List<Opportunity>();
       
           for(integer i = 0; i < 200; i++ ) {
               
               Opportunity opp = new Opportunity( Name='Test Opp' + i,
                                                  CloseDate = System.Today(),
                                                  AccountId = a.id,
                                                  Type = 'New Ad Sales',
                                                  LeadSource = 'Marketing Mailer',
                                                  StageName = 'Discovery' );
                                                  opps.add(opp);
           }

        insert opps;
        
        Test.startTest();           
        OpprtunityBatchUpdate batchJob= new OpprtunityBatchUpdate();
        Database.BatchableContext Bc;
        batchJob.start(Bc);       
batchJob.execute(Bc,opps); batchJob.finish(Bc); Test.stopTest(); } }

 

And the other line it doesn't like is in the class (which the code is above):

 

return Database.getQuerylocator(query);

 

 

 

 

Suresh RaghuramSuresh Raghuram
decrease the size 10 or 5 and see
for(integer i = 0; i < 10; i++ ) { Opportunity opp = new Opportunity( Name='Test Opp' + i, CloseDate = System.Today(), AccountId = a.id, Type = 'New Ad Sales', LeadSource = 'Marketing Mailer', StageName = 'Discovery' ); opps.add(opp); }