[SalesForce] SOQL -Date EQUAL Comparsion -no viable alternative at character ‘ ‘

I am trying a very simple date comparison and not getting it right:

The following errors out with a 'no viable alternative at character ''' when the query is executed:

    Date dueDate = Date.newInstance(2018, 6, 14);
    String copSOQL = 'Select StartDate__c from COP__c where 
 StartDate__c='+dueDate;
    Database.query(copSOQL);

However the same query works fine when used with a bind variable:

Date dueDate = Date.newInstance(2018, 6, 14);  
List<COP__c> = 
    [Select StartDate__c from COP__c where StartDate__c=:dueDate];

Appreciate any help.

Best Answer

You cannot have literal line breaks in Apex string literals. They must be represented as '\n', where you want a line break in the string. If you just want to break a long string in code, use concatenation:

String aLongString = 'SELECT Many_Fields_Here '
                     + 'FROM An_Object__c '
                     + 'WHERE Stuff';

Note the spaces at the ends of lines.

In this case, you can just use static SOQL with an Apex bind. There's no need for the dynamic construction here.

  List<COP__c> cops = [Select StartDate__c,EndDate__c,Fee__c 
                       from COP__c 
                       where StartDate__c = :dueDate];

It's perfectly legal to break a static SOQL query in that way, it lets the platform check the code at compile time, and you don't have to worry about converting your comparison variables to strings and formatting them correctly - the compiler handles all of that.

You generally shouldn't just concatenate (+) data types other than strings into a dynamic query string, because you need to ensure that they're specified in the right format for SOQL. In the case of a Date, you can see what the behavior is by doing 

System.debug(''+Date.newInstance(2018, 6, 14));

What you'll get back is 2018-06-14 00:00:00. If you place that in a SOQL query, 

SELECT Id FROM myObject__c WHERE DateField__c = 2018-06-14 00:00:00

you'll receive a query parsing error, because that's not the right date format for SOQL.

That's why you should use binding: it makes sure you don't have to worry about the formatting issues.

If you cannot use binding, you should explicitly convert the value using String.valueOf(date), which yields the valid format 2018-06-14 and allows the query to complete.

Related Topic