function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
Sumant KuchipudiSumant Kuchipudi 

How can I get dynamic YEAR and MONTH in SOQL?

Hi,
I have below SOQL clause with Static Year and Month, is it possible to have dynamic values in those fields?
(
  ( 
     ( YEAR__c=2016 and Month__c='July' )  or 
     ( YEAR__c=2016 and Month__c='August' ) or 
     ( YEAR__c=2016 and Month__c='September' ) or 
     ( YEAR__c=2016 and Month__c='October' ) or 
     ( YEAR__c=2016 and Month__c='November' ) or  
     ( YEAR__c=2016 and Month__c='December' ) or 
     ( YEAR__c=2017 and Month__c='January' ) or 
     ( YEAR__c=2017 and Month__c='February' ) or 
     ( YEAR__c=2017 and Month__c='March' ) or 
     ( YEAR__c=2017 and Month__c='April' ) or 
     ( YEAR__c=2017 and Month__c='May' ) or 
     ( YEAR__c=2017 and Month__c='June' )
  ) 
  OR YEAR__c=null
)
)

 
Glyn Anderson (Slalom)Glyn Anderson (Slalom)
Sumant,  You can use dynamic values in the query using the ":" notation; however, in your example, you need specific combinations of two fields.  So the following will give you more results than you want:
Set<String> months = new Set<String>
{   'January', 'February', 'March', 'April', 'May', 'June',
    'July', 'August', 'September', 'October', 'November', 'December'
};
Set<String> years = new Set<String>{ '2016', '2017' };

List<My_Object__c> records =
(   [   SELECT  Id
        FROM    My_Object__c
        WHERE   (YEAR__c IN :years AND Month__c IN months) OR YEAR__c = null
    ]
);
To get around this problem, you can add a string formula field (Month_Year__c) to your object that concatenates the month and year:
Month__c + ", " + YEAR__c
Then your query can look like this:
Set<String> monthYears = new Set<String>
{   'July, 2016', 'August, 2016', 'September, 2016', 'October, 2016',
    'November, 2016', 'December, 2016', 'January, 2017', 'February, 2017',
    'March, 2017', 'April, 2017', 'May, 2017', 'June, 2017'
};

List<My_Object__c> records =
(   [   SELECT  Id
        FROM    My_Object__c
        WHERE   Month_Year__c IN :monthYears
    ]
);
You might even come up with a way to dynamically generate the set.

On the other hand, you could define the Month_Year__c field as a date formula:
DATE(
  VALUE( YEAR__c ),
  CASE( Month__c,
    "January", 1,
    "February", 2,
    "March", 3,
    "April", 4,
    "May", 5,
    "June", 6,
    "July", 7,
    "August", 8,
    "September", 9,
    "October", 10,
    "November", 11,
    "December", 12,
    0
  ),
  1
)
And then you can use the SOQL date operators:
Date startDate = Date.newInstance( 2016, 7, 1 );
Date endDate = Date.newInstance( 2017, 6, 1 );

List<My_Object__c> records =
(   [   SELECT  Id
        FROM    My_Object__c
        WHERE   (   Month_Year__c >= :startDate
                AND Month_Year__c <= :endDate
                )
    ]
);
Let me know if you have any questions.
 
Glyn Anderson (Slalom)Glyn Anderson (Slalom)
I've already noticed a few typos in my post.  Sorry about that.  Let me know if anything is unclear.
Sumant KuchipudiSumant Kuchipudi
Hi Glyn Anderson, Thanks for your reply but I want to get dynamic values on SoQL not using Apex. Sorry if my question was wrong. I can do it using apex but we should not use that.