surface↓extraction↓parse_date
Extractionscalar · returns json

PARSE_DATE

Parse date from any format into JSON with fields: year, month, day, iso, formatted, day_of_week, is_valid

Per-row — runs once for each row.

extractionllmdate

Syntax

PARSE_DATE({{ date_text }})
PARSE_DATE({{ date_text }}, '{{ prefer_format }}')

Arguments

nametypedescription
date_textVARCHAR—
prefer_format(optional)VARCHARFor ambiguous dates: mdy (US), dmy (EU), ymd (ISO)

About

Parse date from virtually any format into structured components. Returns JSON — use ->> operator to extract fields: PARSE_DATE(text) ->> 'iso' -- "2024-03-15" PARSE_DATE(text) ->> 'year' -- "2024" Backend: deterministic Python via dateutil.parser + dateparser. - dateutil.parser handles ISO 8601, US/EU formats, natural language - dateparser falls back on fuzzy phrasing ("yesterday", "last Thursday", "il 5 maggio" — 200+ languages) - Two-digit years follow the LLM version's rule: 00–30 → 2000–2030, 31–99 → 1931–1999 - Ambiguous MDY vs DMY is resolved by the `prefer_format` argument (default 'mdy' for US-style); also honors 'dmy' and 'ymd' - Returns `is_valid: false` with NULL fields when nothing parses, so downstream WHERE clauses can filter cleanly For LLM-style parsing (noisier input, domain-specific formats, custom rules), use PARSE_DATE_LLM — see parse_date_llm.cascade.yaml.

Examples

Date parsed from text

SELECT
  parse_date ('March 15th, 2024')

ISO date

SELECT
  parse_date ('2024-03-15')

US slash format

SELECT
  parse_date ('03/15/2024', 'mdy')

European slash format

SELECT
  parse_date ('15/03/2024', 'dmy')

Nearby rabbit holes

same domain
scalar

CRAWL_BATCH

Crawl a website and extract structured data from each page (via Firecrawl)

extractionexternal-apiurl
scalar

EXTRACT

Extract specific information from unstructured text (zero-shot NER)

extractionnlispecialist-zootext
scalar

EXTRACT_LLM

LLM-backed extraction (escape hatch for EXTRACTS)

extractionllmllm-escape-hatchtext
scalar

EXTRACT_STRUCTURED

Extract structured fields from text per a user-supplied schema

extractionllmtext
aggregate

MERGE_TIMELINES

Merge multiple timelines into unified chronological sequence

extractionllmjson
scalar

PARSE

Extract information from text using natural-language instructions

extractionllmtext
Climb back to The Looking Glass