function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Abhinav GuptaAbhinav Gupta 

Does convertTimezone function ever works ?

I tried calling this function from both APEX and WS API, with a simplest soql fragement mentioned in salesforce docs itself i.e.

 

SELECT convertTimezone(CreatedDate) FROM Opportunity

 

I always got this error : MALFORMED_QUERY: Invalid aggregate function: convertTimezone How does this guy works ?

 

I also posted this in general development discussion boards, but no reply till yet, so posting here also. Simon, you there :) ?

Best Answer chosen by Admin (Salesforce Developers) 
sfdcfoxsfdcfox

Per the documentation, you have to use convertTimezone within a date function. Here's an example that works (in the documentation):

 

 

SELECT HOUR_IN_DAY(convertTimezone(CreatedDate)), SUM(Amount)
FROM Opportunity
GROUP BY HOUR_IN_DAY(convertTimezone(CreatedDate))

This finds the sum of all opportunities, grouped by the hour of the day, with a translation accounting for the user's current time zone. They also explicitly mention that this query will not work:

 

 

SELECT convertTimezone(CreatedDate) FROM Opportunity

It doesn't work because there is no date function being applied to the query.

 

The list of date functions that are available are listed here: SOQL Date functions

 

 

 

All Answers

sfdcfoxsfdcfox

Per the documentation, you have to use convertTimezone within a date function. Here's an example that works (in the documentation):

 

 

SELECT HOUR_IN_DAY(convertTimezone(CreatedDate)), SUM(Amount)
FROM Opportunity
GROUP BY HOUR_IN_DAY(convertTimezone(CreatedDate))

This finds the sum of all opportunities, grouped by the hour of the day, with a translation accounting for the user's current time zone. They also explicitly mention that this query will not work:

 

 

SELECT convertTimezone(CreatedDate) FROM Opportunity

It doesn't work because there is no date function being applied to the query.

 

The list of date functions that are available are listed here: SOQL Date functions

 

 

 

This was selected as the best answer
Abhinav GuptaAbhinav Gupta

Thanks SfdcFox, I was in super hurry that day, so just copied the SOQLs and started playing to wrap up the pending.