1

I have some custom metadata used to configure various scheduled batch jobs. The custom metadata fields allow entry of a string that is used in a SOQL query as a date literal filter

For example, the field should support:

YESTERDAY
TODAY
LAST_N_DAYS:45
...

as defined in the SOQL documentation or support an ISO8601 datetime such as 2021-01-06T00:00:00Z

How can I verify that the text entered is a valid date literal using a Validation Rule?

1 Answer 1

2

This is not a perfect solution for reasons listed below but should be useful to many and can be extended for the edge cases

NOT(REGEX(MyField__c,
'(' &
      'YESTERDAY|' &
      'TODAY|' &
      'TOMORROW|' & 
      'LAST_WEEK|' &
      'THIS_WEEK|' &
      'NEXT_WEEK|' &
      'LAST_MONTH|' &
      'THIS_MONTH|' &
      'NEXT_MONTH|' &
      'LAST_90_DAYS|' &
      'NEXT_90_DAYS|' &
      'THIS_QUARTER|' &
      'LAST_QUARTER|' &
      'NEXT_QUARTER|' &
      'THIS_YEAR|' &
      'LAST_YEAR|' &
      'NEXT_YEAR|' &
      'THIS_FISCAL_QUARTER|' &
      'LAST_FISCAL_QUARTER|' &
      'NEXT_FISCAL_QUARTER|' &
      'THIS_FISCAL_YEAR|' &
      'LAST_FISCAL_YEAR|' &
      'NEXT_FISCAL_YEAR' &
      'NEXT_N_DAYS:\\d+|' &
      'LAST_N_DAYS:\\d+|' & 
      'NEXT_N_WEEKS:\\d+|' &
      'LAST_N_WEEKS:\\d+|' &
      'NEXT_N_MONTHS:\\d+|' &
      'LAST_N_MONTHS:\\d+|' &
      'NEXT_N_QUARTERS:\\d+|' &
      'LAST_N_QUARTERS:\\d+|' &
      'NEXT_N_FISCAL_QUARTERS:\\d+|' &
      'LAST_N_FISCAL_QUARTERS:\\d+|' &
      'NEXT_N_YEARS:\\d+|' &
      'LAST_N_YEARS:\\d+|' &
      '\\d{4}-[01]\\d-[0-3]\\dT[0-2]\\d:[0-5]\\d:[0-5]\\d([+-][0-2]\\d:[0-5]\\d|Z)' &
')'
))

ISO 8601 regex reference I used

Caveats

  • Will accept datetimes like 2021-13-01T00:00:00Z // month 13
  • Will accept datetimes like 2021-01-39T00:00:00Z // day 39
  • Does not support timezones other than UTC
  • Is not leap year-aware

I welcome enhancements in other answers.

You must log in to answer this question.

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