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
bohemianguy100bohemianguy100 

date format in dynamic soql

I am running into an error when trying to create a dynamic soql query string with a date variable.  I'm not formatting the date correctly.

 

Here is how I'm building the query:

 

String strSOQL = 'Select Name,Event_Date__c, openSlots__c, Time_of_Event__c, Description__c, Type__c,Service_Line__c, SFDC_Facility__r.Name, Fee__c, ' +
    		'Remaining_Space_Available__c,Maximum_Space_Available__c, Event_Status__c from SFDC_Event__c where (Event_Status__c = \'Open\' or ' +
    		'Event_Status__c = \'Wait List\' or Event_Status__c = \'Closed\') and Service_Line__c like ' + '\'' + serviceLineString + '\'' + ' and ' +
    		'Type__c like ' + '\'' + typeString + '\'' + ' and SFDC_Facility__c = ' + '\'' + facilityString + '\'' + ' and Event_Date__c >= ' + startDate + ' and Event_Date__c <= ' + endDate + ' order by Event_Date__c';

 

Here is the system debug output for the start date:

 

4/01/2011

 

 

And here is the debug of the entire soql string output:

 

Select Name,Event_Date__c, openSlots__c, Time_of_Event__c, Description__c, Type__c,Service_Line__c, SFDC_Facility__r.Name, Fee__c, Remaining_Space_Available__c,Maximum_Space_Available__c, Event_Status__c from SFDC_Event__c where (Event_Status__c = 'Open' or Event_Status__c = 'Wait List' or Event_Status__c = 'Closed') and Service_Line__c like '%'and Type__c like '%' and Event_Date__c >= 4/01/2011 and Event_Date__c <= 4/30/2011 order by Event_Date__c

 

 

How does the start and end date variables need to be formatted?  With single quotes?

 

Any help is appreciated.

Thanks.

Best Answer chosen by Admin (Salesforce Developers) 
Bhawani SharmaBhawani Sharma

you need to modify your date variable in the format "YYYY-MM-DDT00:00:00Z".This is the standard time format and database hold the date and date time values in GMT format.

So the resultant query should be similar to the following:

 

Select Name,Event_Date__c, openSlots__c, Time_of_Event__c, Description__c, Type__c,Service_Line__c, SFDC_Facility__r.Name, Fee__c, Remaining_Space_Available__c,Maximum_Space_Available__c, Event_Status__c from SFDC_Event__c where (Event_Status__c = 'Open' or Event_Status__c = 'Wait List' or Event_Status__c = 'Closed') and Service_Line__c like '%'and Type__c like '%' and Event_Date__c >= 2011-01-04T00:00:00Z and Event_Date__c <= 2011-04-30T00:00:00Z order by Event_Date__c

 

 

All Answers

nhobertnhobert

I would recommend doing this instead:

 

SFDC_Event__c myEvent = new SFDC_Event__c(['Select Name,Event_Date__c, openSlots__c, Time_of_Event__c, Description__c, Type__c,Service_Line__c, SFDC_Facility__r.Name, Fee__c, Remaining_Space_Available__c,Maximum_Space_Available__c, Event_Status__c from SFDC_Event__c where  (Event_Status__c = 'Open'  or Event_Status__c = 'Wait List' or Event_Status__c = 'Closed') and Service_Line__c like :serviceLineString and Type__c like :typeString and SFDC_Facility__c = :facilityString and Event_Date__c >= :startDate and Event_Date__c <=:endDate order by Event_Date__c LIMIT 1'

 

If the Date values are coming from Input Fields ona page and the Field has a Field Type of Date, they should work perfectly.

Also by Field =:Variable syntax like Event_Date__c >= :startDate you will limit the chance of sql injection queries.

 
bohemianguy100bohemianguy100

thank you for your reply, but unfortunately, I have to use dynamic soql.  I have some additional complexity in the query that require the use of dynamic soql.

 

How can I correctly format the date to use within the dynamic soql syntax.
Bhawani SharmaBhawani Sharma

you need to modify your date variable in the format "YYYY-MM-DDT00:00:00Z".This is the standard time format and database hold the date and date time values in GMT format.

So the resultant query should be similar to the following:

 

Select Name,Event_Date__c, openSlots__c, Time_of_Event__c, Description__c, Type__c,Service_Line__c, SFDC_Facility__r.Name, Fee__c, Remaining_Space_Available__c,Maximum_Space_Available__c, Event_Status__c from SFDC_Event__c where (Event_Status__c = 'Open' or Event_Status__c = 'Wait List' or Event_Status__c = 'Closed') and Service_Line__c like '%'and Type__c like '%' and Event_Date__c >= 2011-01-04T00:00:00Z and Event_Date__c <= 2011-04-30T00:00:00Z order by Event_Date__c

 

 

This was selected as the best answer