function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
MM0MM0 

Dynamic SOQL Question

I am am new to SF and am having a problem building a simple query and am not sure what the problem is. A similar statement works fine in another service. I just want to compare two dates but I keep getting an error that the criterion is not a date and should not be enclosed in quotes. I'm sure the answer is really simple.

 

        Integer endPadTime   = (DAY_IN_MILLI * POST_DAYS);
        Long currentTime  = Datetime.now().getTime();
                
        Datetime endDate   = Datetime.newInstance( currentTime + endPadTime );

 

 

        String queryString =
                            'select id, name, status__c, account__c, alias__c, out_of_service_date__c, ' +
                            'model_number__c, serial_number__c, unque_id__c ' +
                            'from EF_Equipment__c ' +
                            'where out_of_service_date__c < \'' + endDate.format('yyyy-MM-dd') + '\'';  // the problem is here

 

Thanks

 

 

 

 

 

 

Best Answer chosen by Admin (Salesforce Developers) 
sfdcfoxsfdcfox

First of all, do not surround the literal date value in quotes:

 

 

select {fields} from {object} where {datefield} = {yyyy-MM-dd}

Ex:

select id from EF_Equipment__c where Out_Of_Service_Date__c < 2012-10-31

Secondly, If Out_Of_Service_Date__c is a DateTime field, make sure you are using the following date format:

 

 

 

{yyyy}-{MM}-{dd}T{hh}:{mm}:{ss}.{SSS}{tz}

Ex:

2012-10-31T00:00:00.000Z
2012-10-30T17:00:00.000-07:00

Thirdly, It's probably a better idea to simply use a "bind variable" as seen in languages such as SQL that has a "?" parameter. In SOQL, this is done by direct inline substitution:

 

 

 

String queryString = 'select id,name,... from EF_Equipment__c where out_of_service_date__c < :endDate';

The "colon-variable" syntax inside of a string causes Apex Code SOQL to attempt to magically shoehorn the value(s) inside the variable. This inline substitution does not work except for SOQL queries, though (unlike a language like PHP, which performs substitution in any substitutable string). You will also need to make sure that the data type of the variable matches the data type of the field in order to avoid runtime errors.

 

All Answers

sfdcfoxsfdcfox

First of all, do not surround the literal date value in quotes:

 

 

select {fields} from {object} where {datefield} = {yyyy-MM-dd}

Ex:

select id from EF_Equipment__c where Out_Of_Service_Date__c < 2012-10-31

Secondly, If Out_Of_Service_Date__c is a DateTime field, make sure you are using the following date format:

 

 

 

{yyyy}-{MM}-{dd}T{hh}:{mm}:{ss}.{SSS}{tz}

Ex:

2012-10-31T00:00:00.000Z
2012-10-30T17:00:00.000-07:00

Thirdly, It's probably a better idea to simply use a "bind variable" as seen in languages such as SQL that has a "?" parameter. In SOQL, this is done by direct inline substitution:

 

 

 

String queryString = 'select id,name,... from EF_Equipment__c where out_of_service_date__c < :endDate';

The "colon-variable" syntax inside of a string causes Apex Code SOQL to attempt to magically shoehorn the value(s) inside the variable. This inline substitution does not work except for SOQL queries, though (unlike a language like PHP, which performs substitution in any substitutable string). You will also need to make sure that the data type of the variable matches the data type of the field in order to avoid runtime errors.

 

This was selected as the best answer
MM0MM0

Thanks for the formatting tutorial. It was very helpful. I just inherited this code and should have checked a few things first, mainly to make sure that the data types were in agreement (rookie error). Thanks for pushing me in the right direction. I'm sure I will have a few more questions in the future.