Parsing and Formatting

Concepts covered: Date Formatting

Dates arrive as text, and each source picks its own layout: a CSV export writes them one way, a partner feed another. date_parse(text, format) reads text into a TIMESTAMP by matching it against a format string: each % specifier stands for one field, and every other character must appear in the text exactly as written.

Minutes are %i because %m already means month. A format that writes %m in the minutes slot reads that slot as a second month field, and any minute value above 12 fails to parse.

SELECT
  event_id,
  raw_time,
  date_parse(raw_time, '%m/%d/%Y %h:%i %p') AS parsed
FROM raw_events

%h with %p turns 11:50 PM into 23:50 and 07:05 PM into 19:05. The slashes and the colon outside the specifiers matched the text exactly, and so did the space between date and time.

Real feeds drift. Suppose a second upstream system starts writing 2024-03-10 01:30 into the same column. That text has hyphens where the format expects slashes, so date_parse raises an error at that row and the whole query fails, however many good rows came before it. TRY wraps any expression and turns an error inside it into NULL for that row, the way TRY_CAST handles a bad cast.

SELECT
  event_id,
  raw_time,
  TRY(date_parse(raw_time, '%m/%d/%Y %h:%i %p')) AS parsed
FROM raw_events

With that row added to raw_events, the 3 rows above parse as before and the new one returns NULL in parsed. Counting those NULLs after each load tells you how many rows the format missed, and a count that jumps from 0 to thousands overnight means a source changed its layout.

The format string decides ambiguous dates

The text 03/04/2024 means March 4 to a US system and April 3 to most of Europe. Nothing in its 10 characters says which, so the format string records the decision, and only the source system's documentation can settle it.

Month first

Reads month then day and returns 2024-03-04.

SELECT
  date_parse('03/04/2024', '%m/%d/%Y') AS parsed

Day first

Reads day then month and returns 2024-04-03, a month later, with no error raised.

SELECT
  date_parse('03/04/2024', '%d/%m/%Y') AS parsed

Pattern letters: the second format language

parse_datetime(text, pattern) and format_datetime(value, pattern) use letter patterns in place of % codes, as in yyyy-MM-dd HH:mm:ss. Letter case carries meaning: MM is month and mm is minutes, so the pattern yyyy-mm-dd reads 2024-03-10 as minute 3 of January 10. parse_datetime returns a TIMESTAMP WITH TIME ZONE, and its Z letter reads a numeric offset such as -0600 from the text.

SELECT
  parse_datetime('2024-03-10 01:30:00 -0600', 'yyyy-MM-dd HH:mm:ss Z') AS parsed,
  parse_datetime('2024-03-10 01:30:00 -0600', 'yyyy-MM-dd HH:mm:ss Z') AT TIME ZONE 'UTC' AS parsed_utc,
  format_datetime(TIMESTAMP '2024-03-10 07:30:00 UTC', 'yyyy/MM/dd/HH') AS path_part

The offset in the text becomes part of the value, so converting to UTC lands on 07:30. Writing runs the same machinery in reverse: format_datetime turns a timestamp into 2024/03/10/07, an hour-level folder layout for raw files. Code that reads those folder names back needs the identical pattern, so define it once and use it in both directions.

The case slip in yyyy-mm-dd raises no error, so nothing in the load stops it. These 2 columns parse the same text and differ only in the case of the month letters.

SELECT
  parse_datetime('2024-03-10', 'yyyy-MM-dd') AS month_upper,
  parse_datetime('2024-03-10', 'yyyy-mm-dd') AS month_lower

month_lower lands on January 10 at 00:03: the mm slot read 03 as minutes, and the month, never read, defaulted to January. Every row of a feed parsed this way counts as parsed, so a NULL check passes while the whole feed piles into January.

Do

• Write the format string from a documented sample of the source, field by field
• Wrap parsing in TRY and alert when the NULL count rises after a load
• Keep the raw text column next to the parsed one so failures can be inspected

Avoid

• Guess day-first or month-first from a handful of rows
• Mix % specifiers and pattern letters in one format string
• Write mm for months in a letter pattern; it means minutes

Which format string makes date_parse('2024-03-10 13:05:00', ...) return 2024-03-10 13:05:00?

• '%Y-%m-%d %H:%m:%s'
• '%Y-%m-%d %h:%i:%s'
• 'yyyy-MM-dd HH:mm:ss'
• '%Y-%m-%d %H:%i:%s'

About this lesson

This section is part of the Dates: Advanced lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.