A quickie example of two date-handling tricks in SOQL: this query lists the number of Opportunities created each day this month.

SELECT DAY_ONLY(CreatedDate), COUNT(Id)
FROM Opportunity
WHERE CreatedDate = THIS_MONTH
GROUP BY DAY_ONLY(CreatedDate)
ORDER BY DAY_ONLY(CreatedDate)

The two cool things going on here:

1. the DAY_ONLY() function removes the timestamp part of CreatedDate. That’s but one of a bunch of handy Date functions in SOQL.
   • remember that the functions that end in _QUARTER() or _MONTH() don’t include the year, so you may want to pair them with a *_YEAR() function.
     • For example, the badboy below groups Opportunities by the year-and-quarter they were created over the last three years:
       SELECT CALENDAR_YEAR(CreatedDate), CALENDAR_QUARTER(CreatedDate), COUNT(Id)
FROM Opportunity
WHERE CreatedDate = LAST_N_YEARS:3
GROUP BY CALENDAR_YEAR(CreatedDate), CALENDAR_QUARTER(CreatedDate)
ORDER BY CALENDAR_YEAR(CreatedDate), CALENDAR_QUARTER(CreatedDate)
2. the THIS_MONTH and LAST_N_YEARS items in the WHERE clauses above are date literals, and there are a whole gaggle of handy ones.
   • I haven’t thought of a simple way, in declarative SOQL, to perfectly say “going back to today’s date, X years ago”. Unless the number of years ago is divisible by 4, you’re likely to include or exclude one day too many, because of leap years.
     • WHERE CreatedDate = LAST_N_DAYS:365 might be a day too few!
     • WHERE CreatedDate = LAST_N_DAYS:366 might be a day too many!
     • WHERE CreatedDate = LAST_N_DAYS:1461 (that is, 365 * 4 + 1) should always work!

Thanks to Francisco Figueroa Hernández for reminding me of this!