How to Format Dates in BigQuery

How do I use FORMAT_DATE in BigQuery?

FORMAT_DATE and FORMAT_DATETIME turn DATE or DATETIME values into readable strings with custom patterns.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

Why format dates in BigQuery?

Formatted dates improve report readability, allow locale-specific displays, and support string concatenation in emails or exports.

Which functions format dates?

Use FORMAT_DATE for DATE, FORMAT_DATETIME for DATETIME, and FORMAT_TIMESTAMP for TIMESTAMP.

How do I format an order date to YYYY-MM?

Call FORMAT_DATE('%Y-%m', Orders.order_date). This returns values like 2024-06.

Can I include day names?

Yes. FORMAT_DATE('%A, %d %B %Y', Orders.order_date) outputs Saturday, 22 June 2024.

What are common pattern tokens?

%Y=four-digit year, %m=two-digit month, %d=day, %A=weekday name, %B=month name.

How do I convert strings to DATE before formatting?

Wrap the string with PARSE_DATE('%Y-%m-%d', string_col), then pass the result to FORMAT_DATE.

Best practice: store raw dates

Keep dates in native DATE/DATETIME types. Only format in the final SELECT to avoid breaking date math and filtering.

Why How to Format Dates in BigQuery is important

How to Format Dates in BigQuery Example Usage


-- Show each customer's last purchase in a friendly format
SELECT
  Customers.name,
  FORMAT_DATE('%A, %d %B %Y', MAX(Orders.order_date)) AS last_order
FROM `project.dataset.Orders` AS Orders
JOIN `project.dataset.Customers` AS Customers
  ON Orders.customer_id = Customers.id
GROUP BY Customers.name;

How to Format Dates in BigQuery Syntax


FORMAT_DATE(format_string, date_expression)
FORMAT_DATETIME(format_string, datetime_expression)
FORMAT_TIMESTAMP(format_string, timestamp_expression)
-- Example with ecommerce table
SELECT FORMAT_DATE('%Y-%m-%d', Orders.order_date) AS order_day
FROM `project.dataset.Orders`;

Common Mistakes

Formatting before filtering. Using FORMAT_DATE in WHERE disables partition pruning. Filter with raw DATE first, then format in SELECT.
Using wrong pattern tokens. For a two-digit month use %m, not %M (minutes). Mismatched tokens return NULL. Check the pattern reference.