Have a custom field that contains a date range in the following format:
3/16/20 - 2/22/20
What I need to do is separate this one line into two different fields, the first selection/range and the second range, so if you take the screenshot I need it to be separated to 3/16/20
for one field and 3/22/20
for the other field.
Currently I have this and something is causing an error randomly and I want to make sure it is not the SQL statement
For the first selection, I use the following:
TO_DATE(LTRIM(SUBSTR({custbody_shipwindow}, 1,(INSTR({custbody_shipwindow}, '-')-1))),'mm/dd/yy')
For the second selection I use the following:
TO_DATE(LTRIM(SUBSTR({custbody_shipwindow},(INSTR({custbody_shipwindow}, '-')+1), LENGTH({custbody_shipwindow}))),'mm/dd/yy')
Advertisement
Answer
Try:
TO_DATE(REGEXP_SUBSTR(TRIM({custbody_shipwindow}),'^[^ -]+'),'MM/DD/YY') TO_DATE(REGEXP_SUBSTR(TRIM({custbody_shipwindow}),'[^ -]+$'),'MM/DD/YY')
or to be safe, but possibly not return the date:
TO_DATE(REGEXP_SUBSTR(TRIM({custbody_shipwindow}),'^[0-9]{1,2}/[0-9]{1,2}/[0-9]{1,2}'),'MM/DD/YY') TO_DATE(REGEXP_SUBSTR(TRIM({custbody_shipwindow}),'[0-9]{1,2}/[0-9]{1,2}/[0-9]{1,2}$'),'MM/DD/YY')