09 May 20 · npack · #Bigquery ·   Bookmark   ×

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

npack

posted on 09 May 20

Enjoy great content like this and a lot more !

Signup for a free account to write a post / comment / upvote posts. Its simple and takes less than 5 seconds