[SalesForce] pass date from vf page input date to soql Query

I have written vf page with input date filed. When I change input date the value needs to pass in query and compare with createddate then I want to display matching records in page.

I have done almost but when I am using Createddate >: dat then only records are displaying. When I used Createddate =:dat for that exact date it is not. I found reason for this.because Createddate is the date+time but we are passing date only. As a end user can not pass exact time with date. I want to display records based on user selected date automatically.

Code:

Page

<apex:pageBlockSection >
        <apex:pageBlockSectionItem >
            Date: <apex:input type="date" value="{!dat}" onChange="pickDate()"/>
        </apex:pageBlockSectionItem>
    </apex:pageBlockSection>
  <apex:pageBlockSection title="Criteria">

Controller

public class DisplayInputDateRecordsCtrl{

 public Date dat{get;set;}
 public List<Invoice__c> Invoices {get; set;}

 public PageReference executeSearch()
 {

  Invoices =[select id, createddate,Name, Invoice_Total__c,expirydate__c,(SELECT id, name, Quantity__c, Unit_Price__c FROM Line_Items__r)
            from Invoice__c 
            where createddate >: dat limit 20]; 
            //createddate =: dat   --->not displaing records

      return null;
  }
}

Best Answer

You can use DAY_ONLY (not an obvious name for a function that pulls the Date part from a Datetime) from the Date Functions in the where clause:

where DAY_ONLY(convertTimezone(CreatedDate)) > :dat

combined with convertTimezone that deals with Converting Time Zones in Date Functions.

See my colleague's blog post on this subject Gotcha: convertTimezone() must be used in SOQL Date functions dealing with Datetime for more detail.

Related Topic