How to convert string to DATE in BigQuery

Why convert strings to DATE in BigQuery?

DATE columns sort, filter, and aggregate faster than text. Converting string timestamps lets you use date functions like DATE_DIFF, DATE_TRUNC, and partition pruning for cheaper, faster queries.

What syntax converts a string to DATE?

Use PARSE_DATE(format, string) when the text is not ISO-8601. Use CAST(string AS DATE) or DATE(string) when the text matches ISO-8601 (YYYY-MM-DD).

Which format patterns does PARSE_DATE accept?

BigQuery follows strftime patterns: %Y (4-digit year), %m (2-digit month), %d (2-digit day), etc. Combine them to mirror the incoming text exactly.

How do I pick the right approach?

ISO strings: CAST('2023-08-15' AS DATE). Non-ISO strings (e.g., 15/08/2023): PARSE_DATE('%d/%m/%Y', '15/08/2023'). Avoid PARSE_DATE on NULLs by wrapping with SAFE.PARSE_DATE.

Practical example: first order date per customer

Orders.order_date is VARCHAR '08/23/2023'. Convert then aggregate to find each customer’s first purchase date.

WITH converted AS (
SELECT customer_id,
PARSE_DATE('%m/%d/%Y', order_date) AS order_date
FROM `shop.Orders`
)
SELECT customer_id, MIN(order_date) AS first_order_date
FROM converted
GROUP BY customer_id;


Best practices for string-to-DATE conversion

1) Standardize input to ISO-8601 upstream if possible. 2) Use SAFE.PARSE_DATE to get NULL instead of errors. 3) Store dates as DATE, not STRING, to save cost and avoid repeated parsing.

Common mistakes and fixes

Mistake 1: Wrong format pattern causes "Failed to parse input". Fix by matching every literal and delimiter exactly.
Mistake 2: Direct CAST on non-ISO strings returns NULL. Use PARSE_DATE with the correct format or clean the data first.

Quick reference table

TaskRecommended FunctionISO string → DATECAST(col AS DATE)Non-ISO string → DATEPARSE_DATE(format, col)Skip errorsSAFE.PARSE_DATE(format, col)