CODEWITHSUNDEEP
sqlgoogle-bigquery
Jesus Navarro
Jesus Navarro

Reputation: 65

Convert string to date in BigQuery

I am trying to convert the following code, where i grab all the date and filter the follow_up_date column to validate if it has the date format, if yes then copy the date if not insert "Not Set", then i filter to get the values != "Not Set", the issue here is that it is that the follow_up_date column is a string, the new column created follow_up_date_mod is still a string, how can i convert to be a date?

WITH
  _0 AS (
    WITH
      _0 AS (
        SELECT
          *,
          IF(
            RegexP_CONTAINS(
              follow_up_date, r'^[0-9]{2}[/\-][0-9]{2}[/\-][0-9]{4}$'),
            follow_up_date,
            "Not Set") AS follow_up_date_mod
        FROM x.y.z AS _t
      )
    SELECT * FROM _0
  ),
  _1 AS (
    SELECT *
    FROM _0
    WHERE
        (NOT (follow_up_date_mod = "Not Set"))
        OR ((follow_up_date_mod) IS NULL)
    ORDER BY follow_up_date_mod ASC
    LIMIT 30001
  )
SELECT * FROM _1

Thanks in Advance for the help

Upvotes: 0

Views: 782

Answers (1)

pmo511
pmo511

Reputation: 619

Try parse_date and provide the date format within your string:

WITH _0 AS (
WITH
  _0 AS (
    SELECT
      *,
      IF(
        RegexP_CONTAINS(
          follow_up_date, r'^[0-9]{2}[/\-][0-9]{2}[/\-][0-9]{4}$'),
        follow_up_date,
        "Not Set") AS follow_up_date_mod
    FROM (select '01-07-2022' as follow_up_date) AS _t
  )
SELECT * FROM _0
), _1 AS (
SELECT follow_up_date, parse_date('%m-%d-%Y',follow_up_date_mod) as follow_up_date_mod
FROM _0
WHERE
    (NOT (follow_up_date_mod = "Not Set"))
    OR ((follow_up_date_mod) IS NULL)
ORDER BY follow_up_date_mod ASC
LIMIT 30001)
SELECT * FROM _1

Upvotes: 2

Related Questions