0

I am trying to change a hard coded created date time range from a query to a custom setting. When applying the custom setting variable, the list return empty. Here is the original query:

SELECT Id, Name, CreatedDate FROM Account WHERE CreatedDate = LAST_N_DAYS:30 ORDER BY CreatedDate DESC

Here is how the query should look like:

  SELECT Id, Name, CreatedDate
  FROM Account
  WHERE CreatedDate = :creationDate
  ORDER BY CreatedDate DESC

The custom setting has the label of Number of days and the API name is numberOfDays__c with a number field label Report Days Range with the API name of Report_Days_Range__c. The Default org value is 30 and no setup owner as seen in the image below. [Custom Setting Values1

Default Organization Level Value

Here is my code:

numberOfDays__c customSetting = numberOfDays__c.getInstance();
Integer customSettingReportDaysRange = Integer.valueOf(
  customSetting.Report_Days_Range__c
);
Date creationDate = System.today() - customSettingReportDaysRange;

List<Account> accountReportList;

accountReportList = [
  SELECT Id, Name, CreatedDate
  FROM Account
  WHERE CreatedDate = :creationDate
  ORDER BY CreatedDate DESC
];

//Debug statements to confirm that everything is working
System.debug(
  'Custom Setting number value: ' + customSettingReportDaysRange
);
System.debug('Custom Setting date: ' + creationDate);
System.debug('Account report list: ' + accountReportList);

I have tried the solution offered in another post: Using SOQL apex bind variables for date literals (e.g. LAST_N_DAYS, TODAY, THIS_QUARTER) however, the query returns empty.

The main goal is to return a report of accounts where the time created was in the past 30 days.

Thank you in advance

2
  • WHERE CreatedDate = :creationDate -- are you trying to get only Accounts created exactly 30 days ago? or Accounts created 30,29,28, ...0 days ago? (if the latter, you need a >= operator. Also CreatedDate is a Datetime field, not a Date
    – cropredy
    Aug 16, 2023 at 0:03
  • Thank you so much! You solved it. I can't believe I had the wrong DateTime type! Since I needed the lasted accounts created within the past 30 days I will use >= Aug 16, 2023 at 0:36

1 Answer 1

2
  • CreatedDate is DateTime, not Date
  • You probably want all Accounts created within the last 30 days, not exactly 30 days ago

so ...

DateTime creationDate = System.now().addDays(-customSettingReportDaysRange);

List<Account> accountReportList;

accountReportList = [
  SELECT Id, Name, CreatedDate
  FROM Account
  WHERE CreatedDate >= :creationDate
  ORDER BY CreatedDate DESC
];

or, in 1 line

Account[] accountReportList = 
   SELECT Id, Name, CreatedDate
     FROM Account
     WHERE CreatedDate >= :System.now().addDays(-customSettingReportDaysRange)
     ORDER BY CreatedDate DESC
    ];
2
  • Can I ask why -1 * customSettingReportDaysRange if addDays(-customSettingReportDaysRange) and System.today() - customSettingReportDaysRange solves it Aug 16, 2023 at 1:46
  • they all work; I edited my answer. Thanks
    – cropredy
    Aug 16, 2023 at 17:45

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .