A Date Is Not a String

With the drink names cleaned, The Daily Grind's next question is easy to state: how did the oat latte sell in March? That is a count per month, and counting per month means grouping by month, which means having a month. The order log doesn't have one. It has a day typed on every row, as text that happens to look like a date, and one row doesn't even manage that.

Watch it happen

Six rows of order_log:

from pyspark.sql import functions as F

looks_like_a_date = F.col("ordered_on").rlike("^[0-9]{4}-[0-9]{2}-[0-9]{2}$")

result = (
    order_log
    .withColumn("ordered_on", F.when(looks_like_a_date, F.to_date("ordered_on", "yyyy-MM-dd")))
    .withColumn("month_start", F.trunc("ordered_on", "month"))
    .filter(F.col("month_start") == "2026-03-01")
    .select("order_id", "drink", "ordered_on", "month_start")
)

Three orders in March, and ordered_on is now a real date, which is what makes month_start possible.

Parse first

F.to_date(column, "yyyy-MM-dd") turns text into a date, given the pattern the text follows. F.to_timestamp does the same when there is a time of day. Until you do this, the engine is holding characters, and F.month of a string is a parse in disguise.

One bad row stops everything. Order 3006 was typed as 18/04/2026. Parse the column with that row still in it and the whole run fails, not just the row. That is why the example parses inside a when: F.when(looks_like_a_date, F.to_date(...)) runs to_date only on the rows that match the pattern and leaves the others null, and a null month_start is then dropped by the filter like any other. It is strict where it should be, and it is better than the alternative: a date that silently became null with nobody deciding so.

Why not .filter(looks_like_a_date) first and then parse? Because the engine plans the whole chain before it runs any of it, as the Nothing Runs Until You Ask lesson said, and here it merges that filter with the month filter into one test evaluated on every row, so to_date meets 18/04/2026 after all and the run fails. Measured on Sail; the plan shows the merged FilterExec. A guard travels with the parse; a filter can be moved.

Sail follows current Spark here, where bad input is an error: since Spark 4.0, ANSI mode is on by default, and a to_date that cannot parse its input fails. Before that, with ANSI mode off, it returned null, so code written against older defaults may have been hiding bad rows for years. Spark's escape hatch, try_to_date, is not available on Sail: reaching for it gives UnsupportedOperationException: unknown function: try_to_date, and the when guard is the way through.

Then the date functions work

F.year("ordered_on")            # 2026, an integer
F.month("ordered_on")           # 3, an integer
F.date_add("ordered_on", 7)     # a week later, still a date
F.datediff(end, start)          # whole days between two dates, end first

Note the types. F.month returns the integer 3; F.date_format(col, "MM") returns the string "03", and the grader will not call those equal.

Text sorts as text

Year-first text like 2026-03-02 sorts in date order by luck of the format. Day-first text does not:

December comes first as text, because 01 sorts before 02. Parse with F.to_date("day", "dd/MM/yyyy") and January does.

Two problems, in the order they build:

1. Orders in March — keep the rows that parse, parse them, pick a month.
2. Membership Length — parse two dates and measure between them.