0

So I can run a query such as

WHERE Date__c > 2020-07-31

but then next year "2020" would be out of date. I'd like "2020" to be variable so that next year, when I run this again, it will automatically be set to "2021".

So essentially I need my year to be variable and for it to always be equal to last year. It should be:

WHERE Date__c > [last year]-07-31

Is there any way to do this (without using Apex since this would be used in Conga)?

I saw that there's a LAST_YEAR function in SOQL but I can't write "LAST_YEAR-07-31" for some reason.

To clarify, my Date__c field is of type Date.

2 Answers 2

3

You can use the following Date Functions:

  • CALENDAR_MONTH
  • CALENDAR_YEAR
  • DAY_IN_MONTH

Which in combination would look like:

WHERE CALENDAR_YEAR(Date__c) = 2021
AND CALENDAR_MONTH(Date__c) = 7
AND DAY_IN_MONTH(Date__c) = 31

If you want it to be always a specific date last year, you need a Date Literal as well. That would change your query filter to:

WHERE Date__c = LAST_YEAR
AND CALENDAR_MONTH(Date__c) = 7
AND DAY_IN_MONTH(Date__c) = 31

I misread the > as =, the above can be simplified to:

WHERE Date__c = LAST_YEAR
AND CALENDAR_MONTH(Date__c) > 7
3
  • Thank you! So my current criteria are "WHERE Date__c > 2020-07-30 AND Date__c < 2021-07-31". How could I make the year part of both criteria variable in this scenario?
    – Guest
    Commented Sep 27, 2021 at 14:35
  • Basically it should always be "greater than [last year]-07-30 and lesser than [this year]-07-31" but I'm not quite sure how to get there.
    – Guest
    Commented Sep 27, 2021 at 15:27
  • Are you trying to figure out within a specific fiscal year or?
    – Adrian Larson
    Commented Sep 27, 2021 at 15:32
3

you can use SOQL Date Literals: with a combination of date functions. For your current example:

WHERE Date__c = LAST_YEAR
AND CALENDAR_MONTH(Date__c) >= 8
0

You must log in to answer this question.

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