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.
FORMAT_DATE('%b %Y', date)returns text such as Jun 2024.%bis the short month name and%Yis the four-digit year.FORMAT_DATE('%G-W%V', date)returns the ISO year and ISO week number, such as 2024-W23.CAST(date AS STRING)always returns the plain form YYYY-MM-DD.ORDER BYon a formatted label sorts alphabetically. Sort on the underlying date instead.
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.