Why is this an invalid expression in a formula(Date), when the REGEXP_SUBSTR({percentcompleteoverride.period} returns 'Jan'? TO_DATE(TRIM(REGEXP_SUBSTR({percentcompleteoverride.period}, '^[^\d\s]+\s+'))||' 1 '||'2025', 'MON DD YYYY')

AI generated answer (using internal Slack threads):

The expression is invalid because the

REGEXP_SUBSTR({percentcompleteoverride.period}, '^[^\d\s]+\s+')

part of the formula is expected to return a string that represents a month (like 'Jan', 'Feb', etc.). However, the

TRIM()

function is used to remove leading and trailing spaces, which means if the

REGEXP_SUBSTR()

function returns 'Jan', the

TRIM()

function will not alter it. The problem arises when you try to concatenate this result with ' 1 ' and '2025' to form a date string and then convert it to a date using the

TO_DATE()

function. The

TO_DATE()

function expects a string in the format 'MON DD YYYY', but the concatenated string is not in this format because there is an extra space before '1'. The correct formula should be:

TO_DATE(TRIM(REGEXP_SUBSTR({percentcompleteoverride.period}, '^[^\d\s]+\s+'))||'1'||'2025', 'MON DD YYYY')

This will correctly form a date string like 'Jan12025', which can be converted to a date using the

TO_DATE()

function. ****** Disclaimer: This response was generated by an artificial intelligence assistant based on previous conversations from multiple Slack channels, and may contain inaccuracies or mistakes. ******

You literally told me my formula was incorrect and then told me to use the exact same formula.