Pages

Monday, November 25, 2013

Salesforce SOQL query LAST_WEEK ; LAST_N_DAYS:n ; N_DAYS_AGO:n


LAST_WEEK
Using criteria LAST_WEEK in SOQL will return all records where the date starts from 12:00:00 AM (user Local Time Zone) on the first day of the week before the most recent first day of the week and continues for seven full days. First day of the week is determined by your locale.
Example: my user locale setting is English (United States) and today = Sat, 23 Nov 2013, I run query below:
SELECT Id, Name, CreatedDate FROM Account WHERE CreatedDate = LAST_WEEK ORDER BY CreatedDate DESC
this query will return accounts created between 10-16 Nov 2013, because for Locale is US English, a week runs Sunday to Saturday, whereas with UK English, a week spans Monday to Sunday.

SELECT Id, Name, CreatedDate FROM Account WHERE CreatedDate < LAST_WEEK ORDER BY CreatedDate DESC
this query will return accounts created before 10 Nov 2013.

LAST_N_DAYS:7
Using LAST_N_DAYS:7 in SOQL will return all records where the date starts from 12:00:00 AM (user Local Time Zone) of the current day and continues for the last 7 days.
SELECT Id, Name, CreatedDate FROM Account WHERE CreatedDate = LAST_N_DAYS:7 ORDER BY CreatedDate DESC
If today is Friday, 22 Nov 2013, using LAST_N_DAYS:7 will return account created between 16 - 22 Nov 2013, while LAST_WEEK will return account created between 10 - 16 Nov 2013.

N_DAYS_AGO:7
Using N_DAYS_AGO:7 in SOQL will return all records where the date starts from 12:00:00 AM (user Local Time Zone) on the day 7 days before the current day and continues for 24 hours. (The range does not include today.)
SELECT Id, Name, CreatedDate FROM Account WHERE CreatedDate = N_DAYS_AGO:7 ORDER BY CreatedDate DESC
If today is Monday, 25 Nov 2013, using N_DAYS_AGO:7 will return account only created on 18 Nov 2013. 


Please note all Date field return in SOQL query will be in GMT format, example: 2013-11-18T08:05:21.000Z, so you need to convert it as needed.