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

Question

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 1

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())

Also useful Migrating from legacy SQL