1

I am trying to query an aggregated result but since one of my field(HLA_Expiration_Date__c ) is formula field(Date) its returning Date Time. is there any way to convert the value of this field into Date from Date Time in SOQL quey itself.

enter image description here

Integer count = 1;
        for (AggregateResult ar : [Select Count(id) c , HLA_Expiration_Date__c exp from Hardware_Loan_Agreement__c where HLA_Expiration_Date__c>=:Date.today() and HLA_Expiration_Date__c<:Date.today().addDays(5) and Primary_Status__c = 'Proposal' Group By HLA_Expiration_Date__c Order by HLA_Expiration_Date__c]) 
        {
            if(count ==1){
                date1 = ((Date)ar.get('exp')).format();
                cnt1 = (Integer)ar.get('c'); 
            }else if (count == 2){
                date2 = ((Date)ar.get('exp')).format();
                cnt2 = (Integer)ar.get('c');
            }else if (count == 3){
                date3 = ((Date)ar.get('exp')).format();
                cnt3 = (Integer)ar.get('c');
            }else if (count == 4){
                date4 = ((Date)ar.get('exp')).format();
                cnt4 = (Integer)ar.get('c');
            }else if (count == 5){
                date5 = ((Date)ar.get('exp')).format();
                cnt5 = (Integer)ar.get('c');
            }
            count++;
        }

Since here when i am collecting the count of records for respective dates it always give only one as count since the dates are same but their time is different.

6

Use the DAY_ONLY SOQL Function.

Select Count(id) c , DAY_ONLY(HLA_Expiration_Date__c) exp from 
     Hardware_Loan_Agreement__c where HLA_Expiration_Date__c>=:Date.today() and 
     HLA_Expiration_Date__c<:Date.today().addDays(5) and Primary_Status__c = 
     'Proposal' Group By DAY_ONLY(HLA_Expiration_Date__c) Order by 
     DAY_ONLY(HLA_Expiration_Date__c)

Returns a date representing the day portion of a dateTime field

https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_select_date_functions.htm

Bit confusing if just reading the name of the function as it seems like it is just the day but is is the actual date only portion of the date/time

  • Hi @Eric, Getting Compile Error: only datetime fields support: DAY_ONLY at line 52 column 35 – RAT__ Jan 30 '17 at 7:38
  • @RAT__ the function only works on datetime fields which is what you said you needed to get the date from. If the field is already a date only then your question does not make sense. Maybe change your formula to only grab the datevalue from the final calculation? – Eric Jan 30 '17 at 12:07
  • if you will notice in my image its showing the field as Formula (Date) but actually its returning me Date Time value. That is what the problem i am facing. – RAT__ Jan 30 '17 at 13:31
  • @RAT__ then force it to return date and wrap formula in datevalue. Otherwise submit a bug report. I forget if the formula field type of date is a ui thing or an actual database thing. If a ui thing then it may be storing a date time internally unless you wrap in datevalue – Eric Jan 30 '17 at 13:32
3

My colleague has a blog post Gotcha: convertTimezone() must be used in SOQL Date functions dealing with Datetime that suggests that this is typically the way to go:

DAY_ONLY(convertTimezone(HLA_Expiration_Date__c))

so that the DateTime is first shifted into the user's timezone and then the Date part taken. Otherwise the Date could be a day out.

  • Hi @Keith, Getting Compile Error: only datetime fields support: DAY_ONLY at line 52 column 35 – RAT__ Jan 30 '17 at 7:37
  • @RAT__ That is the error you get of HLA_Expiration_Date__c is a Date not a DateTime field and re-reading your question that does sound to be the case. You will need a different field that is a DateTime to be able to group by time; at present your data model is missing the time information. – Keith C Jan 30 '17 at 9:13

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.