convert string to date and validate date in Big Query

Question

Hello everyone I am trying to filter a table by date, in order to do that i need to convert a field from a table set as string to date (follow_up_date), currently i have a table like this:

Follow Up Date	Case ID
-	1234
na	1235
null	1236
15/02/2022	1237
24/05/2022	1238

As you can see, there are fields in the date format but set as string, tried using

MAX(PARSE_DATE("%d/%m/%Y", follow_up_date)) AS __ordering__0

But is not working, as for the other fields which instead of a date have anyting else like -, na, etc, to set a standar value like "not set"

The query that i need to modify is:

WITH
  _0 AS (
    SELECT
      follow_up_date AS __follow_up_date__1, case_id_url AS __case_id_url__1
    FROM x.y.z AS _t
    GROUP BY __follow_up_date__1, __case_id_url__1
    ORDER BY __follow_up_date__1 ASC
    LIMIT 30001
  )
SELECT * FROM _0

Any thoughts? I will really appreciate the help.

Thanks a lot in advance!

user_5221944 · Answer

Use below example for how to properly convert string to date

with data as (
  select '-' follow_up_date, '1234' case_id union all
  select 'na', '1235' union all
  select null, '1236' union all
  select '15/02/2022', '1237' union all
  select '24/05/2022', '1238' 
)
select *, 
  safe.parse_date("%d/%m/%Y", follow_up_date) as fixed_date
from data

with output

convert string to date and validate date in Big Query

1 Answers