[SalesForce] Using soql query how to get all events from startdatetime to enddatetime in apex

Hi i want to get the all the events in between startdatetime to enddatetime.but i am not getting the records.

My code is:

@RemoteAction
    public static string eventdata(Datetime starts,Datetime ends){
 for(Event evnt: [select Id, Subject, isAllDayEvent, StartDateTime, EndDateTime from Event where  ownerid=:userinfo.getUserId() and startdatetime>=:starts and enddatetime<=:ends]){
}
}

in the vfpage:function getEventData()
{

        // records are retrieved from soql database
        Visualforce.remoting.Manager.invokeAction(
        '{!$RemoteAction.WisconsinCalendarController.eventdata}', 
            // controller and method names
            start.toUTCString(),
            end.toUTCString(),
            function(result, event)
            {
                console.log(result);
                if (event.status) 
                {

In the select query i am not getting values.

The values of starts is 2016-04-22 00:00:00 ends is 2016-04-30 00:00:00
I tried in the query editor if the starts and ends value format like 2016-04-22T00:00:00Z then i am getting the values but in the coding i give starts and ends values like above format i am getting Invalid date/time: 2016-04-21T20:00:00Z

Best Answer

You are trying to pass String variables for Datetime parameters. Change your method to accept String parameters instead. In order to be able to use Datetime.valueOf, you will need to use a format like 2016-04-22 00:00:00 instead. You will then need to perform the conversion to Datetime yourself:

public static void myMethod(String startTime, String endTime)
{
    Datetime startDt = Datetime.valueOfGmt(startTime);
    Datetime.endDt = Datetime.valueOfGmt(endTime);
    // other logic
}

Related Solutions

[SalesForce] Can a SOQL query selecting all columns be generated at runtime

Absolutely there is. Use the following syntax:

// write your query string exactly as in a SOQL statement minus the square-brackets
string queryString = 'SELECT id,Name,OpportunityId FROM Account WHERE OpportunityId!=null LIMIT 5';

// then use database.query() method.  
// Note its return value is a list<sObject> though you can cast it appropriately

list<sObject> sAccountList=database.query(queryString);
list<Account> accountList=(list<Account>)database.query(queryString);

You can also reference instance variables, but not any variable of an instance

set<id> myIdSet;

string queryString = 'SELECT id,Name FROM Account WHERE id IN:myIdSet';
list<Account> accountList = (list<Account>)database.query(queryString);

// XXXXXXXX   The following WONT work    X_X

map<id,Account> accountMap = new map<id,Account>(accountList);

string anotherQueryString = 'SELECT id,Name FROM Account WHERE id IN:accountMap.keyset()';  // X_X WILL NOT WORK 

list<Account> emptyAccountList = (list<Account>)database.query(anotherQueryString);
system.assert(emptyAccountList.isEmpty());

And since you mentioned there being no equivalant to SELECT * FROM ... in SOQL, here's some code I've written to get around that. You'll need to dig into the code to see how it all works. It's very handy when starting a project.

    //=================   QUERY UTIL METHODS   =================================

//#################   Format Fields For Query   ###########################################
public static string FormatFieldsForQuery(sObject M, string prefix){
    if(string.isNotBlank(prefix)){
        if(prefix.endsWith('__c'))
            prefix=prefix.replace('__c','__r.');   
        if(!prefix.endsWith('.'))
            prefix+='.';
    }
    string fields = '';
    for(string f : M.getSObjectType().getDescribe().fields.getMap().keySet())
        fields+=prefix+f+',';
    return fields.removeEnd(',');
}//END FormatFieldsForQuery(sObject M, string prefix)

public static string FormatFieldsForQuery(sObject M){
    return FormatFieldsForQuery(M,'');
}//END FormatFieldsForQuery(sObject M)

public static string FormatFieldsForQuery(string objectName){
    return FormatFieldsForQuery(objectName,'');
}//END FormatFieldsForQuery(string objectName)

public static string FormatFieldsForQuery(string objectName,string prefix){
    if(string.isNotBlank(prefix)){
        if(prefix.endsWith('__c'))
            prefix=prefix.replace('__c','__r.');   
        if(!prefix.endsWith('.'))
            prefix+='.';
    }
    string fields = '';
    sObjectType objectType=Schema.getGlobalDescribe().get(objectName);
    if(objectType==null)
        return fields;
    for(string f :objectType.getDescribe().fields.getMap().keySet())
        fields+=prefix+f+',';
    return fields.removeEnd(',');  
}//END FormatFieldsForQuery(string objectName,string prefix)

public static string FormatFieldsForQuery(map<String,Schema.SObjectField> m,string prefix){ 
    if(string.isNotBlank(prefix)){
        if(prefix.endsWith('__c'))
            prefix=prefix.replace('__c','__r.');   
        if(!prefix.endsWith('.'))
            prefix+='.';
    }
    string fields = '';
    for(string f : m.keySet())
        fields+=prefix+f+',';
    return fields.removeEnd(',');
}//END FormatFieldsForQuery(map<String,Schema.SObjectField> m,string prefix)

public static string AllFieldsQuery(string objectName){
    return 'SELECT '+FormatFieldsForQuery(objectName)+' FROM '+objectName+' ';
}//END AllFieldsQuery

public static string AllFieldsQuery(sObject s){
    return 'SELECT '+FormatFieldsForQuery(s)+' FROM '+s.getSObjectType().getDescribe().getName()+' ';
}//END AllFieldsQuery

Test Code:

//#################   Format Fields For Query   ###########################################
@isTest private static void testFormatFieldsForQuery(){
    // Test order (depending upon input:
    //
    // #1 (sObject M, string prefix)
    // #2 (sObject M)
    // #3 (string objectName)
    // #4 (string objectName,string prefix)
    // #5 (map<String,Schema.SObjectField> m,string prefix)
    // #6 AllFieldsQuery(string objectName)   <---- different method name ----
    ///////////////////////////////////////////////////////////////////////////
    test.startTest();
    //test Account Standard Fields
    Account a=new Account();
    assertFFFQ(Portal.FormatFieldsForQuery(a,''),fffqTestObj.Account);
    assertFFFQ(Portal.FormatFieldsForQuery(a),fffqTestObj.Account);
    assertFFFQ(Portal.FormatFieldsForQuery('Account'),fffqTestObj.Account);
    assertFFFQ(Portal.FormatFieldsForQuery('Account',''),fffqTestObj.Account);
    assertFFFQ(Portal.FormatFieldsForQuery(Schema.SObjectType.Account.fields.getMap(),''),fffqTestObj.Account);
    assertFFFQ(Portal.AllFieldsQuery('Account'),fffqTestObj.Account);

    //test Contact Standard Fields
    Contact c=new contact();
    assertFFFQ(Portal.FormatFieldsForQuery(c,''),fffqTestObj.Contact);
    assertFFFQ(Portal.FormatFieldsForQuery(c),fffqTestObj.Contact);
    assertFFFQ(Portal.FormatFieldsForQuery('Contact'),fffqTestObj.Contact);
    assertFFFQ(Portal.FormatFieldsForQuery('Contact',''),fffqTestObj.Contact);
    assertFFFQ(Portal.FormatFieldsForQuery(Schema.SObjectType.Contact.fields.getMap(),''),fffqTestObj.Contact);
    assertFFFQ(Portal.AllFieldsQuery('Contact'),fffqTestObj.Contact);

    // bad objectName for 
    // #3 (string objectName)
    // #4 (string objectName,string prefix)
    system.assert(string.isBlank(Portal.FormatFieldsForQuery('This shouldn\'t work!!!')));

}//END testFormatFieldsForQuery2

public enum fffqTestObj {Account,Contact}

public static void assertFFFQ(string fieldString,fffqTestObj objName){
    set<string> fields=new set<string>(fieldString.removeStart('SELECT ').substringBefore(' FROM').split(','));

    if(objName==fffqTestObj.Account){
        system.assert(fields.contains('name'));
        system.assert(fields.contains('accountnumber'));
        system.assert(fields.contains('ownerid'));
        system.assert(fields.contains('site'));
        system.assert(fields.contains('accountsource'));
        system.assert(fields.contains('annualrevenue'));
        system.assert(fields.contains('createdbyid'));
        system.assert(fields.contains('jigsaw'));
        system.assert(fields.contains('description'));
        system.assert(fields.contains('numberofemployees'));
        system.assert(fields.contains('fax'));
        system.assert(fields.contains('industry'));
        system.assert(fields.contains('lastmodifiedbyid'));
        system.assert(fields.contains('ownership'));
        system.assert(fields.contains('parentid'));
        system.assert(fields.contains('phone'));
        system.assert(fields.contains('rating'));
        system.assert(fields.contains('sic'));
        system.assert(fields.contains('sicdesc'));
        system.assert(fields.contains('tickersymbol'));
        system.assert(fields.contains('type'));
        system.assert(fields.contains('website'));
    }
    else if(objName==fffqTestObj.Contact){
        system.assert(fields.contains('accountid'));
        system.assert(fields.contains('assistantname'));
        system.assert(fields.contains('assistantphone'));
        system.assert(fields.contains('birthdate'));
        system.assert(fields.contains('ownerid'));
        system.assert(fields.contains('createdbyid'));
        system.assert(fields.contains('jigsaw'));
        system.assert(fields.contains('department'));
        system.assert(fields.contains('description'));
        system.assert(fields.contains('donotcall'));
        system.assert(fields.contains('email'));
        system.assert(fields.contains('hasoptedoutofemail'));
        system.assert(fields.contains('fax'));
        system.assert(fields.contains('hasoptedoutoffax'));
        system.assert(fields.contains('homephone'));
        system.assert(fields.contains('lastmodifiedbyid'));
        system.assert(fields.contains('lastcurequestdate'));
        system.assert(fields.contains('lastcuupdatedate'));
        system.assert(fields.contains('leadsource'));
        system.assert(fields.contains('mobilephone'));
        system.assert(fields.contains('otherphone'));
        system.assert(fields.contains('phone'));
        system.assert(fields.contains('reportstoid'));
        system.assert(fields.contains('title'));  
    }
}//END assertFFFQ

[SalesForce] Using Date in SOQL to get only records created on a certain date

A likely cause of this is implicit conversion of CreatedDate to a Date producing an unexpected result because of the timezone set for your user. According to this Gotcha: convertTimezone() must be used in SOQL Date functions dealing with Datetime blog post by my colleague, the right expression to use for the date part of the "where" is:

DAY_ONLY(convertTimezone(CreatedDate)) = :fromDate

Best Answer

Related Solutions

[SalesForce] Can a SOQL query selecting all columns be generated at runtime

[SalesForce] Using Date in SOQL to get only records created on a certain date

Related Topic