FORMAT_DATE and CAST

Reports need labels that read well, such as Jun 2024, and text that still comes out in the right order. FORMAT_DATE builds the label.

How it works

FORMAT_DATE takes a date and a pattern and returns a STRING laid out the way the pattern says. The result is text, so it sorts as text. Sort on the real date and format only the column you show.

Common mistake

Sorting by the formatted label puts Apr before Jan, because A comes before J. A chart of those rows ends up in alphabetical order instead of calendar order.

Worked example

SELECT FORMAT_DATE('%d %B %Y', completed_date) AS long_label,
  CAST(completed_date AS STRING) AS plain_text
FROM `learn.mot.fact_mot_test`
WHERE completed_date = '2024-06-10'
LIMIT 3

%d is the day of the month and %B is the full month name, so the label comes out as 10 June 2024.

CAST gives the fixed YYYY-MM-DD form for the same date.

All BigQuery lessons