TO_DATE

Parse a string into a DATE using an explicit or default format pattern.

Category: datetimeReturns: DATEDialect: Standard

Syntax

TO_DATE(str [, fmt])

Description

## Overview Parses a string into a DATE using a format pattern. When the format is omitted, the input is expected to be in ISO 8601 (yyyy-MM-dd) and a strict parse is performed. Use TO_DATE to convert string-typed date columns into DATE values for proper comparison and arithmetic. For TIMESTAMP inputs use TO_TIMESTAMP, which accepts the same family of format tokens plus time fields. ## Behavior - Returns a DATE. - Returns NULL if either argument is NULL. - Raises an error if the string does not match the pattern or produces an invalid date (for example February 30 or month 13). - The format string uses Java SimpleDateFormat style tokens (yyyy, MM, dd), not percent-prefixed strftime tokens. - Time-of-day tokens in the format are ignored; only the date portion is produced. - Two-digit year tokens (yy) are interpreted using a pivot year; prefer four-digit yyyy for unambiguous input. ## Format specifiers - **yyyy**: four-digit year (2025). - **yy**: two-digit year, interpreted via pivot year. - **MM**: two-digit month (01-12). - **M**: one or two digit month (1-12). - **MMM**: abbreviated month name (Jan, Feb, ...). - **MMMM**: full month name (January, February, ...). - **dd**: two-digit day-of-month (01-31). - **d**: one or two digit day (1-31). - **D**: day-of-year (1-366). - **EEE**: abbreviated weekday name (Mon, Tue, ...). - **EEEE**: full weekday name. ## Compatibility - Java SimpleDateFormat pattern family. The default format (no fmt argument) follows ISO 8601 (yyyy-MM-dd).

Parameters

Name	Type	Description
`str`		Specifies the string to parse as a date.
`fmt`		Specifies the format pattern describing the input string. When omitted, ISO 8601 (yyyy-MM-dd) is assumed.

Examples

-- ISO 8601 string, no format argument
SELECT TO_DATE('2025-03-15') AS d;

-- Day-first format
SELECT TO_DATE('15/03/2025', 'dd/MM/yyyy') AS d;

-- Month-first format
SELECT TO_DATE('03-15-2025', 'MM-dd-yyyy') AS d;

-- Parse a column value with a dotted format
SELECT TO_DATE(date_str, 'yyyy.MM.dd') AS parsed_date
FROM staging.raw.daily_feed;

-- Edge case: leap day
SELECT TO_DATE('2024-02-29', 'yyyy-MM-dd') AS leap_day;

-- Group revenue by parsed date
SELECT TO_DATE(txn_date_str, 'yyyy-MM-dd') AS d, SUM(amount) AS total
FROM staging.finance.ledger_feed
GROUP BY d
ORDER BY d;

Pitfalls

Format tokens are Java SimpleDateFormat style (yyyy-MM-dd). strftime-style tokens (%Y-%m-%d) are not accepted by TO_DATE; use DATE_FORMAT for percent-style patterns.
Two-digit years (yy) use a pivot year. A value like '25' may parse as 1925 or 2025 depending on the current pivot; always prefer yyyy for unambiguous input.
Invalid dates raise errors rather than returning NULL. Wrap the call in TRY or use conditional validation if your input feed may contain malformed rows.
The 'dd/MM/yyyy' and 'MM/dd/yyyy' formats are visually similar but produce different parses. A value like '03/04/2025' parses as March 4 in US-style and April 3 in European-style; be explicit about the intended format for each feed.
Locale-dependent tokens (MMM for month name, EEE for weekday name) are parsed in English by default. Non-English names require an explicit locale.