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
boyd_rboyd_r 

Dates in Dynamic SOQL

I have a Controller that lets the User pass in a date (date of birth) and I'm trying to use Dynamic SOQL. The problem is that the query string insists in adding the date with 00:00:00 so a DoB or 1971-02-16 gets 00:00:00 appended. This causes the query to fail because the Date of Birth field is a Date only field.

 

I even tried this:

 

 

date d = date.newInstance(dob.year(), dob.month(), dob.day());

 

 

to try and force the removal of the time part before passing it to the query string, but the debug log shows the query passed looks like this:

 

Select id, Name, Date_of_Birth__c,  From myObject__c WHERE  Date_of_Birth__c = 1971-02-16 00:00:00

 

which fails. Pasting the above into the Schema explorer shows a similar failure - remove the 00:00:00 bit and it works fine

 

Help! :smileysad:

 

 

Best Answer chosen by Admin (Salesforce Developers) 
boyd_rboyd_r

Okay so thinking about this further. This problem was arising from a testMethod. In the test class a record was created which include this:

 

myObject__c o = [Name='blah', Date_of_Birth__c=Date.parse('16/02/1971'). Perhaps thats the issue. Howvever heres the solution to get the test code to work:

 

 

Integer y = dob.year();
Integer m = dob.month();
Integer d = dob.day();

//Heres the format we need to create to get the query not append the time element to the query
//(CALENDAR_YEAR(Date_of_Birth__c) = 1978 AND CALENDAR_MONTH(Date_of_Birth__c) = 4 AND DAY_IN_MONTH(Date_of_Birth__c) = 18)
			
if (searchQuery.length() != 0){
				
searchQuery = searchQuery + ' AND (CALENDAR_YEAR(Date_of_Birth__c) =' + y;
searchQuery = searchQuery + ' AND CALENDAR_MONTH(Date_of_Birth__c) =' + m;
searchQuery = searchQuery +  ' AND DAY_IN_MONTH(Date_of_Birth__c) =' + d + ')';
				
}

//Rest of code omitted

 

This code works but its a lot of hard work for a simple type of query