SOQL Notes

Date Functions

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)

convertTimezone()

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

References

1.Force.com SOQL and SOSL Reference

Questions

1. 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

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

Solution

Create a formula field (dt_createdDate__c) returning Date and taking in DateTime;

Now: