Community! I am trying to get this syntax to work and I need some assistance. I need to remove everything in the API field and just get the date in the column. I am struggling to accomplish this and not sure how to write this. Any rewrites would be appreciated. Thanks Lads!
[EXTENDED_DATA] API Field: {"redeterminationDate":"2023-01-01 12:00:00"}
Output Needed as date: 2023-01-01
,REGEXP_SUBSTR(EXTENDED_DATA,'[^.-,]+[^0-9]+[^.-,]+[^0-9]+[^.-,]+[^0-9]'1,9) AS DATE
If that's the consistent structure, you can just substring it out. We can use instr to find the second colon and add 2 to get our starting position:
substr(<column>,instr(<column>,':',1,2)+2,10)