SOQL Date Literals and Date Functions

When there is a need to compare a custom or standard Date or Datetime field on an Objects in Salesforce, it is often difficult to compare a date with an exact date or datetime value. It is not only time consuming for user but also cumbersome to note the exact date value being compared.

Salesforce Object Query Language overcomes this by offering Date Literal expressions that allows a user to compare date or datetime fields to fixed expressions.

Let’s dive deep into how some of these can prove to be highly valuable when querying by date filters.

Date Literals

Definition: A date literal is a fixed expression that represents a relative range of time, such as last month, this week, or next year.

Day

If you wish to compare a date or datetime field to a particular day or range of days then use one of the following literal expressions:

Week

If you wish to compare a date or datetime field to a particular week or range of weeks then use one of the following literal expressions:

Month

If you wish to compare a date or datetime field to a particular month or range of months then use one of the following literal expressions:

Quarter

If you wish to compare a date or datetime field to a particular quarter or range of quarters then use one of the following literal expressions:

Year

If you wish to compare a date or datetime field to a particular quarter or range of quarters then use one of the following literal expressions:

Date Functions

Date Functions become an added benefit when using date literals to compare in a SOQL query. Some of our favourite and most useful Date Functions are below:

Tips & Tricks

Some tips and tricks that will be useful when trying to query accurate data based on date and datetime fields.

• DateTime field values are stored as Coordinated Universal Time (UTC). SOQL queries return DateTime field values as UTC values. If you want to process these values in different time zones, your application might need to handle the conversion.
  • The simplest way to convert is to use the following expression: convertTimezone(datetimeField)
  • Note that you can only use convertTimezone() in a date function. The following query doesn’t work because there is no date function.

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

• To filter a SOQL query on a datetime field with a day value, use the DAY_ONLY SOQL operator.

  • SELECT Id 
    FROM Account 
    WHERE DAY_ONLY(CreatedDate) = TODAY

CONCLUSION

Ideally one should use the above date literals and functions to ensure accurate data is retrieved and the cumbersome process of adding exact dates can be removed. However, if it is necessary to use exact date or datetime values, then the format of these should be specific to what is accepted by Salesforce.