str2date.sql

CREATE TEMPORARY FUNCTION STR2DATE(x STRING) RETURNS DATE
AS (
  CASE
    WHEN REGEXP_CONTAINS(
           x,
           r'(19|20)[[:digit:]]{2}\-*(0[1-9]|1[0-2])\-*(0[1-9]|[12][0-9]|3[01])')
      THEN
           DATE(CAST(SUBSTR(REPLACE(x, '-', ''), 1, 4) AS INT64),
                CAST(SUBSTR(REPLACE(x, '-', ''), 5, 2) AS INT64),
                CAST(SUBSTR(REPLACE(x, '-', ''), 7, 2) AS INT64))
    ELSE
      null
  END
);

WITH dates AS (
  SELECT '2021-12-21' as x UNION ALL -- OK
  SELECT '20220102' as x UNION ALL   -- OK
  SELECT '11111111' as x UNION ALL   -- null 1900年より前
  SELECT '00001212' as x UNION ALL   -- null 1900年より前
  SELECT '20220022' as x UNION ALL   -- null 月が範囲外
  SELECT '20221322' as x UNION ALL   -- null 月が範囲外
  SELECT '20220100' as x UNION ALL   -- null 日が範囲外
  SELECT '20220132' as x UNION ALL   -- null 日が範囲外
  SELECT '20221014.0' as x UNION ALL -- OK ドット以降は捨てる
  SELECT '' as x UNION ALL           -- null 桁が足りない
  SELECT '123' as x UNION ALL        -- null 桁が足りない
  SELECT 'abcd-ef-gh' as x        　 -- null 数字じゃない
)

SELECT
  x, STR2DATE(x)
FROM dates