google-bigquery format date as mm/dd/yyyy in query results

sql date format google-bigquery

Eric Hendershott · Oct 20, 2016 · Viewed 26.6k times · Source

I am using Bigquery SQL to generate a report. The standard Bigquery date format is yyyy-mm-dd, but I want it to be formatted as mm/dd/yyyy.

Is there a way via Bigquery SQL to convert the date format on SELECT?

Thanks in advance,

Answer

In BigQuery Legacy SQL

SELECT 
  STRFTIME_UTC_USEC("2016-10-20", "%m/%d/%Y"),   
  STRFTIME_UTC_USEC(CURRENT_DATE(), "%m/%d/%Y")

In BigQuery Standard SQL (see Enabling Standard SQL)

SELECT 
  FORMAT_DATE("%m/%d/%Y", DATE "2016-10-20"), 
  FORMAT_DATE("%m/%d/%Y", CURRENT_DATE())