in my date column some rows i have 10/12/2007 and in some rows 10-12-2007 now i want to transfer the date into 2007-12-10 in big query

Question

i want to change date format in big query while doing transformations in refine table actually i have date column in my date column some rows i have 10/12/2007 and in some rows 10-12-2007 now i want to change the date into 2007-12-10 this format.

when i am trying some of the commands like SELECT FORMAT_DATE('%Y-%m-%d', PARSE_DATE('%Y/%m/%d', REPLACE(your_date_column, '-', '/'))) AS new_date_column FROM your_table; it showing errors

Answer 1

The format in parse_date does not match the sample dates.

WITH
  sample AS (SELECT * FROM UNNEST(["10/12/2007","10-12-2007"]) AS date_col )
SELECT
  FORMAT_DATE('%Y-%m-%d', PARSE_DATE('%d/%m/%Y', REPLACE(date_col, '-', '/'))) AS new_date_column,
  FORMAT_DATE('%Y-%m-%d', PARSE_DATE(if( contains_substr(date_col, '/') , '%d/%m/%Y','%d-%m-%Y'), date_col)) AS new_date_column,
  
FROM   sample