Allow to group or filter data by date-periods (like day, calendar month, fiscal year)
SELECT CALENDAR_YEAR(CreatedDate), SUM(Amount)
FROM Opportunity
GROUP BY CALENDAR_YEAR(CreatedDate)
We can use convertTimezone() in a date function to convert dateTime fields to the user’s time zone.
Example: Note convertTimezone() to be used in date function, example: HOUR_IN_DAY
SELECT HOUR_IN_DAY(convertTimezone(CreatedDate)) hr, SUM(Amount) sum
FROM Opportunity
GROUP BY HOUR_IN_DAY(convertTimezone(CreatedDate))
You can't use convertTimezone() on directly on DateTime field. Following query will not work:
SELECT convertTimezone(CreatedDate)
FROM Opportunity
1.Force.com SOQL and SOSL Reference
- Why this query does not work?
error: Date-aggregate functions can only be selected in grouped queries*
SELECT CALENDAR_YEAR(CreatedDate), Amount
FROM Opportunity
You can't use a date function in a SELECT clause unless you also include it in the GROUP BY clause
You can use a date function in a WHERE (filter) clause to filter the results even if the ** query doesn't include a GROUP BY clause**. The following query returns data for the year 2010:
SELECT CreatedDate, Amount
FROM Opportunity
WHERE CALENDAR_YEAR(CreatedDate) = 2017
LIMIT 2
- Why this query does not work?
error: Unknown error parsing query
SELECT Id, CloseDate, CreatedDate
FROM Opportunity
WHERE CloseDate = 2017-05-15
AND CreatedDate = 2017-07-07
LIMIT 2
Data-type of CloseDate is date while data-type of CreatedDate is datetime
Create a formula field (dt_createdDate__c) returning Date and taking in DateTime;