function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
NervosaNervosa 

How can i create a query to search by date?

Greetings to everyone!

 

I've got a problem that had most of you, i think =)

There is an inputtext on my VF page:

<apex:inputText value="{!NewItemReleaseDate}" id="rdate" label="Release Date" onfocus="DatePicker.pickDate(false, this, false);"/> 

 As you can see users enter date here.

Also there is a PageBlockSection that function as a search block:

        <apex:pageBlockSection title="Search an item by name and\or release date" collapsible="false">      
        <apex:pageBlockSectionItem >
        <apex:panelGrid >
          <apex:outputLabel style="float:left">Name</apex:outputLabel>
              <apex:inputText id="searchText" value="{!searchText}" style="float:left"/>
          <apex:outputLabel style="float:left" >Date</apex:outputLabel>  
          <apex:inputText id="searchDate" value="{!searchDate}" onfocus="DatePicker.pickDate(false, this, false);" style="float:left">
              <apex:param value="{0,date,MM/dd/yyyy}" assignTo="{!searchDate}"/>    
          </apex:inputText>          
          <apex:commandButton id="SearchButton" value="Search" action="{!ViewData}" reRender="FullFunctionalityForm">
              <apex:param value="WHERE name=" assignTo="{!searchStr}"/>
              <apex:param value="" assignTo="{!searchDate}"/>
          </apex:commandButton>
        </apex:panelGrid>
        </apex:pageBlockSectionItem>
      </apex:pageBlockSection>

 Here is a search method from my APEX controller:

   public PageReference ViewData() {
   
       string sortFullExp = sortExpression  + ' ' + sortDirection;
       if(searchText != null) {
           searchstr = 'WHERE Name LIKE \'%' + searchText + '%\' order by ';}
       if(searchDate != null) {
           searchstr = 'WHERE Name LIKE \'%' + searchText + '%\' and CreatedDate =: searchDate  order by ';}              
       items = Database.query('Select id, Name, Item_Price__c, CreatedDate from Item__c ' + searchstr + sortFullExp);

       return null;
   }

 ...and it doesn't work!!! =(

I guess that my issue concerns date formats both that entered when user adds new item and when searching one.

 

Help me please!

 

Thanks in advance.

AmulAmul

how you havr declared your date value means as string if yes please convert your string value to date using  following command

 

Date mycreatedDate=date.valueOf(SearchDate);

 

and then pass it to query. And also please write a debug line which will give you SOQL query line. And then execute the same query line in your debug console.

 

NervosaNervosa

Thank you for reply.

I tried it, but it didn't fix the bug.

I'd like to trace CreatedDate and searchDate variables, but i don't know how to do it =( 

 

Here is my debug log of a try of search by release date:

26.0 APEX_CODE,DEBUG;APEX_PROFILING,INFO;CALLOUT,INFO;DB,INFO;SYSTEM,DEBUG;VALIDATION,INFO;VISUALFORCE,INFO;WORKFLOW,INFO
02:47:20.020 (20109000)|EXECUTION_STARTED
02:47:20.020 (20172000)|CODE_UNIT_STARTED|[EXTERNAL]|066G0000001nJKg|VF: /apex/FullFunctionalityInventory_2
02:47:20.021 (21092000)|VF_DESERIALIZE_VIEWSTATE_BEGIN|066G0000001nJKg
02:47:20.035 (35048000)|VF_DESERIALIZE_VIEWSTATE_END
02:47:20.036 (36573000)|CODE_UNIT_STARTED|[EXTERNAL]|01pG00000031AqE|Fullfunctionality_2 get(items)
02:47:20.036 (36599000)|SYSTEM_MODE_ENTER|true
02:47:20.039 (39025000)|CODE_UNIT_STARTED|[EXTERNAL]|01pG00000031AqE|Fullfunctionality_2 invoke(getitems)
02:47:20.039 (39130000)|METHOD_ENTRY|[1]|01pG00000031AqE|Fullfunctionality_2.Fullfunctionality_2()
02:47:20.039 (39250000)|METHOD_EXIT|[1]|Fullfunctionality_2
02:47:20.039 (39304000)|CODE_UNIT_FINISHED|Fullfunctionality_2 invoke(getitems)
02:47:20.039 (39319000)|CODE_UNIT_FINISHED|Fullfunctionality_2 get(items)
02:47:20.040 (40260000)|CODE_UNIT_STARTED|[EXTERNAL]|01pG00000031AqE|Fullfunctionality_2 get(searchText)
02:47:20.040 (40279000)|SYSTEM_MODE_ENTER|true
02:47:20.040 (40292000)|CODE_UNIT_STARTED|[EXTERNAL]|01pG00000031AqE|searchText
02:47:20.040 (40309000)|CODE_UNIT_FINISHED|searchText
02:47:20.040 (40319000)|CODE_UNIT_FINISHED|Fullfunctionality_2 get(searchText)
02:47:20.040 (40433000)|CODE_UNIT_STARTED|[EXTERNAL]|01pG00000031AqE|Fullfunctionality_2 get(searchDate)
02:47:20.040 (40449000)|SYSTEM_MODE_ENTER|true
02:47:20.040 (40461000)|CODE_UNIT_STARTED|[EXTERNAL]|01pG00000031AqE|searchDate
02:47:20.040 (40474000)|CODE_UNIT_FINISHED|searchDate
02:47:20.040 (40483000)|CODE_UNIT_FINISHED|Fullfunctionality_2 get(searchDate)
02:47:20.040 (40753000)|CODE_UNIT_STARTED|[EXTERNAL]|Fullfunctionality_2 set(searchText,)
02:47:20.040 (40769000)|SYSTEM_MODE_ENTER|true
02:47:20.040 (40787000)|CODE_UNIT_STARTED|[EXTERNAL]|Fullfunctionality_2 set(searchText,)
02:47:20.040 (40839000)|CODE_UNIT_FINISHED|Fullfunctionality_2 set(searchText,)
02:47:20.040 (40852000)|CODE_UNIT_FINISHED|Fullfunctionality_2 set(searchText,)
02:47:20.040 (40989000)|CODE_UNIT_STARTED|[EXTERNAL]|Fullfunctionality_2 set(searchDate,2012-10-24 00:00:00)
02:47:20.041 (41006000)|SYSTEM_MODE_ENTER|true
02:47:20.041 (41026000)|CODE_UNIT_STARTED|[EXTERNAL]|Fullfunctionality_2 set(searchDate,2012-10-24 00:00:00)
02:47:20.041 (41067000)|CODE_UNIT_FINISHED|Fullfunctionality_2 set(searchDate,2012-10-24 00:00:00)
02:47:20.041 (41079000)|CODE_UNIT_FINISHED|Fullfunctionality_2 set(searchDate,2012-10-24 00:00:00)
02:47:20.041 (41313000)|CODE_UNIT_STARTED|[EXTERNAL]|01pG00000031AqE|Fullfunctionality_2 invoke(ViewData)
02:47:20.041 (41390000)|METHOD_ENTRY|[123]|01pG00000031AqE|Fullfunctionality_2.__sfdc_sortExpression()
02:47:20.041 (41442000)|METHOD_EXIT|[123]|01pG00000031AqE|Fullfunctionality_2.__sfdc_sortExpression()
02:47:20.041 (41488000)|METHOD_ENTRY|[124]|01pG00000031AqE|Fullfunctionality_2.__sfdc_searchText()
02:47:20.041 (41526000)|METHOD_EXIT|[124]|01pG00000031AqE|Fullfunctionality_2.__sfdc_searchText()
02:47:20.041 (41543000)|METHOD_ENTRY|[125]|01pG00000031AqE|Fullfunctionality_2.__sfdc_searchText()
02:47:20.041 (41565000)|METHOD_EXIT|[125]|01pG00000031AqE|Fullfunctionality_2.__sfdc_searchText()
02:47:20.041 (41593000)|METHOD_ENTRY|[126]|01pG00000031AqE|Fullfunctionality_2.__sfdc_searchDate()
02:47:20.041 (41628000)|METHOD_EXIT|[126]|01pG00000031AqE|Fullfunctionality_2.__sfdc_searchDate()
02:47:20.041 (41642000)|METHOD_ENTRY|[127]|01pG00000031AqE|Fullfunctionality_2.__sfdc_searchText()
02:47:20.041 (41663000)|METHOD_EXIT|[127]|01pG00000031AqE|Fullfunctionality_2.__sfdc_searchText()
02:47:20.041 (41710000)|SYSTEM_METHOD_ENTRY|[129]|Database.query(String)
02:47:20.044 (44917000)|SOQL_EXECUTE_BEGIN|[129]|Aggregations:0|Select id, Name, Item_Price__c, CreatedDate from Item__c WHERE Name LIKE '%%' and CreatedDate =: SearchDate  order by name ASC
02:47:20.049 (49995000)|SOQL_EXECUTE_END|[129]|Rows:0
02:47:20.050 (50065000)|SYSTEM_METHOD_EXIT|[129]|Database.query(String)
02:47:20.050 (50107000)|METHOD_ENTRY|[133]|01pG00000031AqE|Fullfunctionality_2.__sfdc_searchText(String)
02:47:20.050 (50152000)|METHOD_EXIT|[133]|01pG00000031AqE|Fullfunctionality_2.__sfdc_searchText(String)
02:47:20.050 (50178000)|METHOD_ENTRY|[134]|01pG00000031AqE|Fullfunctionality_2.__sfdc_searchDate(Date)
02:47:20.050 (50213000)|METHOD_EXIT|[134]|01pG00000031AqE|Fullfunctionality_2.__sfdc_searchDate(Date)
02:47:20.050 (50258000)|CODE_UNIT_FINISHED|Fullfunctionality_2 invoke(ViewData)
02:47:20.051 (51181000)|VF_APEX_CALL|SearchButton|{!ViewData}|PageReference: none
02:47:20.056 (56430000)|CODE_UNIT_STARTED|[EXTERNAL]|01pG00000031AqE|Fullfunctionality_2 get(items)
02:47:20.056 (56451000)|SYSTEM_MODE_ENTER|true
02:47:20.056 (56469000)|CODE_UNIT_STARTED|[EXTERNAL]|01pG00000031AqE|Fullfunctionality_2 invoke(getitems)
02:47:20.056 (56525000)|CODE_UNIT_FINISHED|Fullfunctionality_2 invoke(getitems)
02:47:20.056 (56538000)|CODE_UNIT_FINISHED|Fullfunctionality_2 get(items)
02:47:20.059 (59768000)|CODE_UNIT_STARTED|[EXTERNAL]|01pG00000031AqE|Fullfunctionality_2 get(sortExpression)
02:47:20.059 (59789000)|SYSTEM_MODE_ENTER|true
02:47:20.059 (59802000)|CODE_UNIT_STARTED|[EXTERNAL]|01pG00000031AqE|sortExpression
02:47:20.059 (59860000)|CODE_UNIT_FINISHED|sortExpression
02:47:20.059 (59873000)|CODE_UNIT_FINISHED|Fullfunctionality_2 get(sortExpression)
02:47:20.059 (59979000)|CODE_UNIT_STARTED|[EXTERNAL]|01pG00000031AqE|Fullfunctionality_2 get(sortDirection)
02:47:20.059 (59995000)|SYSTEM_MODE_ENTER|true
02:47:20.060 (60010000)|CODE_UNIT_STARTED|[EXTERNAL]|01pG00000031AqE|Fullfunctionality_2 invoke(getsortDirection)
02:47:20.060 (60076000)|METHOD_ENTRY|[105]|01pG00000031AqE|Fullfunctionality_2.__sfdc_sortExpression()
02:47:20.060 (60111000)|METHOD_EXIT|[105]|01pG00000031AqE|Fullfunctionality_2.__sfdc_sortExpression()
02:47:20.060 (60124000)|METHOD_ENTRY|[105]|01pG00000031AqE|Fullfunctionality_2.__sfdc_sortExpression()
02:47:20.060 (60135000)|METHOD_EXIT|[105]|01pG00000031AqE|Fullfunctionality_2.__sfdc_sortExpression()
02:47:20.060 (60192000)|CODE_UNIT_FINISHED|Fullfunctionality_2 invoke(getsortDirection)
02:47:20.060 (60207000)|CODE_UNIT_FINISHED|Fullfunctionality_2 get(sortDirection)
02:47:20.061 (61880000)|CODE_UNIT_STARTED|[EXTERNAL]|01pG00000031AqE|Fullfunctionality_2 get(searchText)
02:47:20.061 (61901000)|SYSTEM_MODE_ENTER|true
02:47:20.061 (61915000)|CODE_UNIT_STARTED|[EXTERNAL]|01pG00000031AqE|searchText
02:47:20.061 (61930000)|CODE_UNIT_FINISHED|searchText
02:47:20.061 (61943000)|CODE_UNIT_FINISHED|Fullfunctionality_2 get(searchText)
02:47:20.062 (62114000)|CODE_UNIT_STARTED|[EXTERNAL]|01pG00000031AqE|Fullfunctionality_2 get(searchDate)
02:47:20.062 (62132000)|SYSTEM_MODE_ENTER|true
02:47:20.062 (62144000)|CODE_UNIT_STARTED|[EXTERNAL]|01pG00000031AqE|searchDate
02:47:20.062 (62158000)|CODE_UNIT_FINISHED|searchDate
02:47:20.062 (62167000)|CODE_UNIT_FINISHED|Fullfunctionality_2 get(searchDate)
02:47:20.063 (63307000)|VF_SERIALIZE_VIEWSTATE_BEGIN|066G0000001nJKg
02:47:20.066 (66453000)|VF_SERIALIZE_VIEWSTATE_END
02:47:20.474 (70477000)|CUMULATIVE_LIMIT_USAGE
02:47:20.474|LIMIT_USAGE_FOR_NS|(default)|
  Number of SOQL queries: 1 out of 100
  Number of query rows: 0 out of 50000
  Number of SOSL queries: 0 out of 20
  Number of DML statements: 0 out of 150
  Number of DML rows: 0 out of 10000
  Number of script statements: 14 out of 200000
  Maximum heap size: 0 out of 6000000
  Number of callouts: 0 out of 10
  Number of Email Invocations: 0 out of 10
  Number of fields describes: 0 out of 100
  Number of record type describes: 0 out of 100
  Number of child relationships describes: 0 out of 100
  Number of picklist describes: 0 out of 100
  Number of future calls: 0 out of 10

02:47:20.474|CUMULATIVE_LIMIT_USAGE_END

02:47:20.070 (70546000)|CODE_UNIT_FINISHED|VF: /apex/FullFunctionalityInventory_2
02:47:20.070 (70555000)|EXECUTION_FINISHED

 

NervosaNervosa

It seems that CreatedDate and searchDate have different formats of date though both added by calendar DatePicker.

NervosaNervosa

Oops, sorry. CreatedDate - isn't custom field. So i don't know its format.

AmulAmul
createddate is datetime and you are passing date value only so please see the changes below in code.


public PageReference ViewData() {
date mysearchdate=date.valueOf(searchDate);

string sortFullExp = sortExpression + ' ' + sortDirection; if(searchText != null) { searchstr = 'WHERE Name LIKE \'%' + searchText + '%\' order by ';} if(searchDate != null) { searchstr = 'WHERE Name LIKE \'%' + searchText + '%\' and CreatedDate >=: mysearchdate order by ';} items = Database.query('Select id, Name, Item_Price__c, CreatedDate from Item__c ' + searchstr + sortFullExp); return null;

}

 

 

sure this will help you.

 

NervosaNervosa

Thanks a lot - it works, but only when condition is CreatedDate >=: mysearchdate and doesn't work for CreatedDate =: mysearchdate. Why is it so?