Skip to content
Advertisement

Need to parse a date range location in a field to two SQLStatements

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')
User contributions licensed under: CC BY-SA
4 People found this is helpful
Advertisement