Discussions
SuiteWorld is the largest annual gathering of the NetSuite community! It will be held in Las Vegas on October 6-9, 2025. Our customers and partners look forward to SuiteWorld every year as a place to hear the latest from NetSuite, get hands-on learning, and connect with each other. Register now!
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')