4

Has anyone ever faced an issue with LAST_N_MONTHS:N in SOQL? As per salesforce doc:

For the number n provided, starts 00:00:00 of the last day of the previous month and continues for the past n months.

Well the below SOQL works fine as it returns data for last 12 months excluding current month:

SELECT Id FROM Account WHERE CreatedDate = LAST_N_MONTHS:12

But if run the below SOQL, it returns data for last 12 months plus current month

SELECT Id FROM Event WHERE ActivityDateTime = LAST_N_MONTHS:12

Is it a bug?

  • 1
    It should not. I just tested both the query by creating new account today and also by creating a new event with ActivityDateTime as today. Both the queries are not returning the newly created account/event record. – Sudipta Deb Dec 29 '18 at 3:42
  • @abhisekSagar can you post a picture of your query results regarding ActivityDateTime query – Santanu Boral Dec 29 '18 at 4:49
4

It's considering the date/time which is stored in the Database which is always in GMT.

When viewing the record from within the Salesforce UI, the value is automatically translated to and displayed in the timezone set on your user record. However, when the data is inserted, updated, or exported the values will always be in UTC or GMT time and are not translated to the running user's timezone.

You can try the following query.

SELECT Id FROM Event WHERE DAY_ONLY(convertTimezone(ActivityDateTime)) = LAST_N_MONTHS:12

It will convert the ActivityDateTime value in GMT and then will compare it to the Date literal. DAY_ONLY method is representing the day portion of a dateTime field.

3

It works as advertised. However, bear in mind that times are transmitted in GMT, not local time, so it's possible to see some records that are 01-Dec-2018 in such a query for users with a negative GMT (GMT-00:15 or more). If you translate them to local time, they should indeed be in November.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.