CODEWITHSUNDEEP
google-bigquery
Balajee Venkatesh
Balajee Venkatesh

Reputation: 1099

Date formatting in BigQuery

I have found many Date functions for Cloud BigQuery and I have a specific case and none of those available functions seem to work. My data would have a date column in 'MMddyy' format and I would like to convert it into 'yyyy-MM-dd' format. How can I achieve this?

Upvotes: 0

Views: 1254

Answers (1)

Willian Fuks
Willian Fuks

Reputation: 11787

This is one possibility:

#standardSQL
WITH data AS(
  SELECT '072817' as dt UNION ALL
  select NULL UNION ALL
  SELECT ''
)

SELECT IF(dt != '', FORMAT_DATE("%Y-%m-%d", PARSE_DATE("%m%d%y", dt)), dt) FROM data

Upvotes: 3

Related Questions