nikhil nikhil - 2 months ago 69
SQL Question

convert string dd-mm-yyyy to date yyyy-mm-dd in bigquery

I have 600 string fields in a table with format eg.,18.05.2015 and i want to convert into date 2015-05-18 in bigquery. I have tried using timestamp() and date() function but it is returning null values

Answer

In Standard SQL

SELECT PARSE_DATE('%d.%m.%Y', '18.05.2015')

the query against table will look like

SELECT PARSE_DATE('%d.%m.%Y', YourDateColumn)
FROM `YourDataset.YourTable`

Added to address 'broken' values

WITH YourTable AS (
  SELECT '18.05.2015' AS dt UNION ALL
  SELECT '#' AS dt 
)
SELECT 
  CASE WHEN REGEXP_CONTAINS(dt, r'\d{2}\.\d{2}\.\d{4}') 
    THEN CAST(PARSE_DATE('%d.%m.%Y', dt) AS STRING)
    ELSE dt 
  END AS new_dt
FROM YourTable

what this does is - process only values that match 18.05.2015 format and leaves any other untouched

I have multiple date columns with 600 records

Making FINAL attempt to interpret your comments - but honestly, still feel like it is not what you have and you are not giving clear picture, so it is best i could make for you!

CREATE TEMPORARY FUNCTION FIX(x STRING)
RETURNS STRING AS (
  CASE WHEN REGEXP_CONTAINS(x, r'\d{2}\.\d{2}\.\d{4}') 
  THEN CAST(PARSE_DATE('%d.%m.%Y', x) AS STRING) ELSE x END);

WITH YourTable AS (
  SELECT '18.05.2015' AS dt_001, '19.05.2015' AS dt_002, '21.05.2015' AS dt_003 UNION ALL
  SELECT '#' AS dt_001, '20.05.2015' AS dt_002, 'abc' AS dt_003
)
SELECT 
  FIX(dt_001) AS new_dt_001,
  FIX(dt_002) AS new_dt_002,
  FIX(dt_003) AS new_dt_003
FROM YourTable
Comments