Formatting date in Google Bigquery results to mm/dd/yyyy or dd-mm-yyyy and other formats

The standard Bigquery date format is yyyy-mm-dd, but you often want it to be formatted into other ways, eg. mm/dd/yyyy. You can do that by using the FORMAT_DATE function.

Syntax:

FORMAT_DATE(format_string, date_expr)

Convert date to US Format mm/dd/yy:

SELECT FORMAT_DATE("%x", DATE "2020-12-25") as US_format;
+------------+
| US_format  |
+------------+
| 12/25/20   |
+------------+

Convert date to US Format mm/dd/yyyy:

SELECT FORMAT_DATE("%m/%d/%Y", DATE "2020-12-25") as US_format;
+------------+
| US_format  |
+------------+
| 12/25/2020 |
+------------+

Convert date to Indian Format dd-mm-yyyy:

SELECT FORMAT_DATE("%d-%m-%Y", DATE "2020-12-25") as IN_format;
+------------+
| IN_format  |
+------------+
| 25-12-2020 |
+------------+

Other popular formats:

SELECT FORMAT_DATE("%b-%d-%Y", DATE "2008-12-25") AS formatted;
+-------------+
| formatted   |
+-------------+
| Dec-25-2008 |
+-------------+

SELECT FORMAT_DATE("%b %Y", DATE "2008-12-25") AS formatted;
+-------------+
| formatted   |
+-------------+
| Dec 2008    |
+-------------+

Format options:

• %Y - The year with century as a decimal number. eg: 2019
• %y - The year without century as a decimal number (00-99)
• %m - The month as a decimal number (01-12)
• %d - The day of the month as a decimal number (01-31)

Alternatively you can use the standard template formats:

• %F - The date in the format %Y-%m-%d
• %x - The date representation in MM/DD/YY format

nVector

posted on 09 May 20