Discussions
SuiteWorld brings thousands of innovators, builders, and leaders together to learn, connect, and shape what’s next. This October, explore how to build a stronger foundation for growth through inspiring keynotes, major product reveals, hands-on sessions, and unforgettable moments—all in one place for our biggest event of the year. Join us
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')
Learn how to Refer A Member | Earn the Answer Accepter Badge | Be the Content Creator of the Quarter | Vote for the content you want to see!