0

When i write 'Select Id, Subject,Status from Task where Subject like \'%'+searchText+'%\' OR Status like \'%'+searchText+'%\' Order By Subject,Status'; it is working but when i include OR ActivityDate like \'%'+searchText+'%\' .

I tried with OR ActivityDate like \'%'+String. valueOf(ActivityDate)+'%\' but still the error occurs.I converted date to string and still no joy. Please help.

      // Apex  

  String qry ='Select Id, Subject,Status from Task where Subject like \'%'+searchText+'%\'  OR Status like \'%'+searchText+'%\' OR ActivityDate like \'%'+searchText+'%\' Order By Subject,Status,ActivityDate';

  SearchResults = Database.query(qry);


     <!-- Page-->

      <apex:pageblocktable value="{!SearchResults}" var="t" rendered="{!NOT(ISNULL(SearchResults))}" align="center">
        <apex:outputLink value="/{!t.Id}">{!t.Subject}</apex:outputLink>
        <apex:column value="{!t.Subject}"/>
        <apex:outputLink value="/{!t.Id}">{!t.Status}</apex:outputLink>
        <apex:column value="{!t.Status}"/>
        <apex:outputLink value="/{!t.Id}">{!t.ActivityDate}</apex:outputLink>
        <apex:column value="{!t.ActivityDate}"/>

4
  • 1
    LIKE operator doesn't work with a Date or DateTime field. What are you expecting with that? You can filter it from a date range
    – highfive
    Jul 17 '14 at 4:40
  • I want user should be able to search Task by Subject,Status, ActivityDate & by OwnerId . have done pagination with Search box and hence need to filter by the above fields .
    – JJoseph
    Jul 17 '14 at 4:47
  • No way dear.. LIKE operator is for String type. You can prompt user to select a date range and use it for the filter. That's the way normally web applications are following for date filters
    – highfive
    Jul 17 '14 at 4:52
  • Thanks for the quick response.How do i get to that date range ? So this means that OwnerId will also not work as LIKE is for String type only? Any modification if you would like to suggest.
    – JJoseph
    Jul 17 '14 at 4:59
3

As per the salesforce documentations, LIKE operator will work only with String type fields.

The LIKE operator is supported for string fields only.

So you can't filter for Id types nor Date types. For Owner, may be you have to filter with FirstName, LastName or some other mechanism based on your requirement.
And for Date fields you can let user to select a date range and apply that in your query. Because Salesforce gives us plenty of Date literals and Date functions to work with SOQL.

  1. Date Formats and Date Literals
  2. Date Functions

e.g.
You can let user to select the month

<apex:selectList id="selectedMonthsOptn" value="{!selectedMonth}" size="1" label="Month:">
    <apex:selectOptions id="monthsOptn" value="{!monthValues}"/>
</apex:selectList>

Controller :

public Integer selectedMonth{get;set;}   
public List<SelectOption> getMonthValues(){
        List<SelectOption> months = new List<Selectoption>();
        months.add(new selectOption('0', ALL_MONTHS));
        months.add(new selectOption('1', 'January'));
        months.add(new selectOption('2', 'February'));
        months.add(new selectOption('3', 'March'));
        months.add(new selectOption('4', 'April'));
        months.add(new selectOption('5', 'May'));       
        months.add(new selectOption('6', 'June'));
        months.add(new selectOption('7', 'July'));
        months.add(new selectOption('8', 'August'));
        months.add(new selectOption('9', 'September'));
        months.add(new selectOption('10', 'October'));   
        months.add(new selectOption('11', 'November'));
        months.add(new selectOption('12', 'December'));      

        return months;
    }    

public String generateDynamicQuery(){
   String myquery = 'SELECT myfields FROM myObj WHERE CALENDAR_MONTH(convertTimeZone(myDateField))=' + selectedMonth+'';
   return myquery;
}

Likewise you can have WEEK_IN_MONTH, DAY_ONLY(), CALENDAR_YEAR() and lot of other functions to filter Date fields base on your requirement.

Enjoy it :)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.