I have a use case where I need a to extract all records of current day and month over last 10 years. For example, today is 14th June 2021, so I need all records of a particular object from

  • 14-6-2021
  • 14-6-2020
  • 14-6-2019
  • 14-6-2018 ...and so on.

The Date field that is to be used is Datetime field.

I have tried using the Calendar_MONTH & DAYS_IN_MONTH function, but I need to pass the value to these function dynamically. As of what I have tried, these function do not take reference value (such as integer ,string, date)

  • 4
    What have you tried? Where are you stuck? Have you read the Date Functions section of the SOQL and SOSL Reference? – David Reed Jun 14 at 15:04
  • try to add details and do some research on your issues. This forum helps fellow programmers to solve issues and not help them to deliver their requirements. – GirishP Jun 14 at 15:36

You can do this in the SOQL:

SELECT ... FROM MyObject__c 
  WHERE CALENDAR_YEAR(DateTime_Field__c) <= 2021 AND
        CALENDAR_YEAR(DateTime_Field__c) > 2011 AND
        CALENDAR_MONTH(DateTime_Field__c) = 6 AND
        DAY_IN_MONTH(DateTime_Field__c) = 14

Other options are available, too; see the Date Functions for more information.

  • Hi, Thanks for input. Is there a way we can pass the values 4 & 6 dynamically. I tried using a reference of Integer string and date. Do the functions work with some other data type ? – Mitesh Jun 16 at 16:03
  • @Mitesh Yes, you can bind to any type of compatible primitive. – sfdcfox Jun 16 at 16:58
  • Hi, I tried using Date, Integer and string data type. I stored the the value say '6' in the data types, but that did not work. Is there something that I'm missing out here ? – Mitesh Jun 21 at 10:48
  • Also, the field that I'm using is a datetime field , could that be the reason ? – Mitesh Jun 21 at 10:52
  • @Mitesh No, it is not the reason. Let me give you an example that definitely works: Integer thisYear = 2021; Integer tenYearsAgo = 2011; Integer month = 6; Integer day = 14; Account[] records = [ SELECT Name FROM Account WHERE CALENDAR_YEAR(CreatedDate) <= :thisYear AND CALENDAR_YEAR(CreatedDate) > :tenYearsAgo AND CALENDAR_MONTH(CreatedDate) = :month AND DAY_IN_MONTH(CreatedDate) = :day ]; – sfdcfox Jun 21 at 13:04

Since you have tagged this question with Apex, it should be relatively straightforward. You can simply generate a List<Date> and populate it with the values you need, then filter on that.

List<Date> dates = new List<Date>();
for (Integer i = 0; i < 6; i++)
List<MyObject__c> records = [
    SELECT ... FROM MyObject__c WHERE Datetime_Field__c IN :dates

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