+ Start a Discussion
JosephJJosephJ 

Cannot include date field in the query

I'm facing a seious issue here for my query :

String qStr = 'Select OwnerId,Subject,Status,Priority from Task where Subject like \'%'+searchText+'%\' OR Status like \'%'+searchText+'%\' Order By Subject,Status limit ' + QueryLimit + ' offset ' + OffsetSize;

Now the issue is , i need to filter this by one more field called "ActivityDate " which is for Task object. The document says , LIKE operator works only on Strings and hence if i'm including OR ActivityDate like \'%'+searchText+'%\'  it will not work.. How can i get this ?  I'm stucked here.Thanks in advance.

public class PagingTasksController1{

    public List<Task> tasks;
    public Integer CountTotalRecords{get;set;}
    public String QueryString {get;set;}
    public Integer OffsetSize = 0;
    private Integer QueryLimit = 5;
    public List<Task> lstTasks;
    public String searchText {get;set;}

    public string sortField = 'Subject';  // default sort column
    private string sApplySOQL = '';

// the current sort direction. defaults to ascending
    public String sortDir {
        get  { if (sortDir == null) {  sortDir = 'asc'; } return sortDir;  }
        set;
    }

    // the current field to sort by. defaults to role name
    public String getsortField() {
        return sortField;
    }

    // the current field to sort by.
    public void setsortField(string value) {
        sortField = value;
    }
            
    // toggles the sorting of query from asc<-->desc
    public void toggleSort() {
        // simply toggle the direction
        sortDir = sortDir.equals('asc') ? 'desc' : 'asc';
      
        integer iIndex = sApplySOQL.indexOf('Order By');
        if (iIndex > -1){
          sApplySOQL = sApplySOQL.substringBefore('Order By');
          sApplySOQL = sApplySOQL + ' Order By ' + sortField + ' ' + sortDir;
        }
        tasks = Database.query(sApplySOQL );
    }
 
    public PagingTasksController1 (){
        //CountTotalRecords= [select count() from Task];
    }
   public List<Task> getTasks(){
        if(tasks == null){
            tasks = new List<Task>();
        }
        return tasks;
    }
    public void findTasks(){
        String qStr2 = 'Select count() from Task where Subject like \'%'+searchText+'%\' OR Status like \'%'+searchText+'%\'';
        CountTotalRecords = Database.countQuery(qStr2);
        queryTasks();
    }
    public void  queryTasks(){
        String qStr = 'Select OwnerId,Subject,Status,Priority from Task where Subject like \'%'+searchText+'%\' OR Status like \'%'+searchText+'%\' Order By Subject,Status limit ' + QueryLimit + ' offset ' + OffsetSize;
        sApplySOQL = qStr;
      
        tasks = Database.query(sApplySOQL );
        //tasks.sort();

    }
    public Boolean getDisablePrevious(){
        if(OffsetSize>0){
            return false;
        }
        else return true;
    }
    public Boolean getDisableNext() {
        if (OffsetSize + QueryLimit < countTotalRecords){
            return false;
        }
        else return true;
    }
    public PageReference Next() {
        OffsetSize += QueryLimit;
        queryTasks();
        return null;
    }
    public PageReference Previous() {
        OffsetSize -= QueryLimit;
        queryTasks();
        return null;
    }
   public PageReference save() {
        update tasks;
        return ApexPages.CurrentPage();
     }
 
    }
logontokartiklogontokartik
Hi James,
I am not sure if I understand but if someone is searching for a date, I would rather add a additional search box for Date only asking the user to pick for a date. And yes you cannot do a search for date using LIKE as it doesnt make any sense. 

Also when you are creating a dynamic SOQL for date please use the format YYYY/MM/DDThh:mm:ssz without any quotes.

JosephJJosephJ
Ahh, i was thinking the same . But how do i implement another date search box here with my implementation ?
logontokartiklogontokartik

You add an additional inputTexts for searching within date range. 

1. Define fromDate & toDate Date types in your controller
2. Add two <apex:inputs for entering the dates
3. when users enters dates and clicks on search, in your queryTasks() method, add the dates to the SOQL,

Hope this helps

Vinit_KumarVinit_Kumar
You should create another text box for Date on VF page and bind it with a variable in controller and use that variable in your where filter .

Hope this helps !!
JosephJJosephJ
I tried to create something like this , but having problem adding it to my queryTasks() , please help.


  <apex:form >     
       <apex:input label="datePicker" value="{!fromDate}" type="auto"/>
    <apex:input label="datePicker" value="{!toDate}" type="auto"/>
  </apex:form>


public class PagingTasksController1
{
     public Date fromDate {get;set;}
     public Date toDate {get;set;}
}

How do i mention in my query here,becuase it says LIKE does not support Dates

String qStr = 'Select OwnerId,Subject,Status,Priority from Task where Subject like \'%'+searchText+'%\' OR Status like \'%'+searchText+'%\' Order By Subject,Status limit ' + QueryLimit + ' offset ' + OffsetSize;

Really appreciate the help.
logontokartiklogontokartik
You need to use >= AND <= operators, your query would be something like
String qStr = 'Select OwnerId,Subject,Status,Priority from Task where Subject like \'%'+searchText+'%\' OR Status like \'%'+searchText+'%\' ';

if(fromDate != null)
qStr += ' AND ActivityDate >= ' + fromDate;

if(toDate != null)
qStr += ' AND ActivityDate <= ' + toDate;


qStr += ' Order By Subject,Status limit ' + QueryLimit + ' offset ' + OffsetSize;

System.debug('**Query Generated**');
System.debug(qStr);

Thank you
JosephJJosephJ
@logontokartik,

Thanks for the information. I feel shy posting this. I'm getting an error Error: Could not resolve field 'ActitvityDate' from <apex:outputField> value binding '{!tsk.ActitvityDate}' in page New_Test_task_Assignment in Vf page.Below is my code.

public class PagingTasksController1{

    public List<Task> tasks;
    public Integer CountTotalRecords{get;set;}
    public String QueryString {get;set;}
    public Integer OffsetSize = 0;
    private Integer QueryLimit = 10;
    public List<Task> lstTasks;
    public String searchText {get;set;}
    public Date fromDate {get;set;}
    public Date toDate {get;set;}

    public string sortField = 'Subject';  // default sort column
    private string sApplySOQL = '';

// the current sort direction. defaults to ascending
    public String sortDir {
        get  { if (sortDir == null) {  sortDir = 'asc'; } return sortDir;  }
        set;
    }

    // the current field to sort by. defaults to role name
    public String getsortField() {
        return sortField;
    }

    // the current field to sort by.
    public void setsortField(string value) {
        sortField = value;
    }
            
    // toggles the sorting of query from asc<-->desc
    public void toggleSort() {
        // simply toggle the direction
        sortDir = sortDir.equals('asc') ? 'desc' : 'asc';
      
        integer iIndex = sApplySOQL.indexOf('Order By');
        if (iIndex > -1){
          sApplySOQL = sApplySOQL.substringBefore('Order By');
          sApplySOQL = sApplySOQL + ' Order By ' + sortField + ' ' + sortDir +  ' limit ' + QueryLimit + ' offset ' + OffsetSize;
        }
        tasks = Database.query(sApplySOQL );
    }
  
    public PagingTasksController1 (){
        //CountTotalRecords= [select count() from Task];
    }

   public List<Task> getTasks(){
        if(tasks == null){
            tasks = new List<Task>();
        }
        return tasks;
    }
   public void findTasks(){
        String qStr2 = 'Select count() from Task where Subject like \'%'+searchText+'%\' OR Status like \'%'+searchText+'%\'';
        CountTotalRecords = Database.countQuery(qStr2);
        queryTasks();
    }
    public void  queryTasks(){
        String qStr = 'Select OwnerId,Subject,Status,Priority,ActivityDate from Task where Subject like \'%'+searchText+'%\' OR Status like \'%'+searchText+ '%\' Order By ' + sortField;

        if(fromDate != null)
           qStr += ' AND ActivityDate >= ' + fromDate;
          

       if(toDate != null)
         qStr += ' AND ActivityDate <= ' + toDate;
         qStr += ' Order By Subject,Status limit ' + QueryLimit + ' offset ' + OffsetSize;
         System.debug('**Query Generated**');
         System.debug(qStr);  

         sApplySOQL = qStr;
         tasks = Database.query(sApplySOQL );
        //tasks.sort();

    }
    public Boolean getDisablePrevious(){
        if(OffsetSize>0){
            return false;
        }
        else return true;
    }
    public Boolean getDisableNext() {
        if (OffsetSize + QueryLimit < countTotalRecords){
            return false;
        }
        else return true;
    }
   public PageReference Next() {
        OffsetSize += QueryLimit;
        queryTasks();
        return null;
    }
    public PageReference Previous() {
        OffsetSize -= QueryLimit;
        queryTasks();
        return null;
    }
   public PageReference save() {
        update tasks;
        return ApexPages.CurrentPage();
     }
 
    }



<apex:page controller="PagingTasksController1" docType="html-5.0">
    <apex:form >
        <apex:pageBlock title="Tasks" id="pgBlock">
           <apex:pageBlockButtons >
               <apex:commandButton action="{!save}" id="saveButton" value="Save"/>
               <apex:commandButton onclick="resetInlineEdit()" id="cancelButton" value="Cancel"/>
           </apex:pageBlockButtons>
 
        <apex:inlineEditSupport showOnEdit="saveButton, cancelButton"
                    hideOnEdit="editButton" event="ondblclick"
                    changedStyleClass="myBoldClass" resetFunction="resetInlineEdit"/>
 
          <apex:inputText id="searchBox" value="{!searchText}"/>
           <apex:commandButton value="Search" reRender="pgTable,pgBlock" action="{!findTasks}"/>
           <apex:pageBlockTable value="{!Tasks}" var="tsk" id="pgTable" >
          
           <apex:input label="datePicker" value="{!fromDate}" type="auto"/>
           <apex:input label="datePicker" value="{!toDate}" type="auto"/>
              
           <apex:column >
             <apex:outputLink value="{!URLFOR($Action.Task.Delete, tsk.id,['retURL'='/apex/New_Test_task_Assignment'])}" >Delete</apex:outputLink>
           </apex:column>
            
          <apex:column headerValue="Subject">
              <apex:facet name="header">
                    <apex:commandLink value="Subject" action="{!toggleSort}" rerender="pgTable" >
                    <apex:param name="sortField" value="Subject" assignTo="{!sortField}"/>
                    <apex:outputPanel rendered="{!BEGINS(sortField,'Subject')}">
                        &nbsp;<apex:image value="{!IF(sortDir = 'asc','/img/arrowDown.gif','/img/arrowUp.gif')}"/>
                    </apex:outputPanel>
                    </apex:commandLink>
                </apex:facet>    
              
                  <apex:outputField value="{!tsk.Subject}"/>
           </apex:column>
        <apex:column headerValue="Status">
              <apex:facet name="header">
                    <apex:commandLink value="Status" action="{!toggleSort}" rerender="pgTable" >
                    <apex:param name="sortField" value="Status" assignTo="{!sortField}"/>
                    <apex:outputPanel rendered="{!BEGINS(sortField,'Status')}">
                        &nbsp;<apex:image value="{!IF(sortDir = 'asc','/img/arrowDown.gif','/img/arrowUp.gif')}"/>
                    </apex:outputPanel>
                    </apex:commandLink>
              </apex:facet>        
          <apex:outputField value="{!tsk.Status}"/>
      </apex:column>
   
         <apex:column headerValue="Priority">
            <apex:outputField value="{!tsk.Priority}"/>
        </apex:column>
       
        <apex:column headerValue="OwnerId">
            <apex:outputField value="{!tsk.OwnerId}"/>
        </apex:column>
       
         <apex:column headerValue="date">
            <apex:outputField value="{!tsk.ActitvityDate}"/>
        </apex:column>
         
    
     </apex:pageBlockTable>
       <apex:pageBlockButtons >
                <apex:commandButton value="Previous" action="{!Previous}" rerender="pgTable,pgBlock"
                                    status="status" disabled="{!DisablePrevious}" />
                <apex:commandButton value="Next" action="{!Next}" reRender="pgTable,pgBlock"
                                    status="status" disabled="{!DisableNext}" />
                <apex:actionStatus id="status" startText="Please Wait..."/>
            </apex:pageBlockButtons>
        </apex:pageBlock>
    </apex:form>
     <apex:enhancedlist type="Activity" height="800" rowsPerPage="50" customizable="False"/>
</apex:page>


I feel its a dumb question, but i'm stucked . Please help
logontokartiklogontokartik
You have it spelled wrong.. its ActivityDate

<apex:column headerValue="date">
            <apex:outputField value="{!tsk.ActitvityDate}"/>
        </apex:column>
JosephJJosephJ
Error Again. This error i'm getting from when and its still no joy with this. That was a spell error with ActivityDate

unexpected token: 'Order'
Error is in expression '{!findTasks}' in component <apex:commandButton> in page new_test_task_assignment: Class.PagingTasksController1.queryTasks: line 83, column 1
Class.PagingTasksController1.findTasks: line 61, column 1
logontokartiklogontokartik
Can you try fixing these issues yourself? You have not removed the order by from your first assignment 

String qStr = 'Select OwnerId,Subject,Status,Priority,ActivityDate from Task where Subject like \'%'+searchText+'%\' OR Status like \'%'+searchText+ '%\' Order By ' + sortField;

you have it at the bottom, after the Wheres are built.


Thank you