NSC | Use REGEXP to convert a String to a Date

Use Case

User has a specific naming convention for their Lot Numbers: ABC-20231025-20211025 where 20231025 is the expiration date and 20211025 is the production date.

User needs to extract the expiration date with this format: MM/DD/YYYY.

Solution

To achieve the requirement, you can use these formula:

• If using Formula (Text):

(SUBSTR(REGEXP_SUBSTR({inventorynumber},'[^-]+$'),5,2)) || '/' || (SUBSTR(REGEXP_SUBSTR({inventorynumber},'[^-]+$'),7)) || '/' || (SUBSTR(REGEXP_SUBSTR({inventorynumber},'[^-]+$'),1,4))

• If using Formula (Date):

TO_DATE(((SUBSTR(REGEXP_SUBSTR({inventorynumber},'[^-]+$'),5,2)) || '/' || (SUBSTR(REGEXP_SUBSTR({inventorynumber},'[^-]+$'),7)) || '/' || (SUBSTR(REGEXP_SUBSTR({inventorynumber},'[^-]+$'),1,4))),'MM/DD/YYYY')