Write a query to return every feed_events.event_id with event_date_text read independently as day_first (DD/MM/YYYY) and month_first (MM/DD/YYYY). Return NULL for a reading that fails, including impossible dates.
Assign verdict in this order:
01iso: A valid year-first date in YYYY-MM-DD or YYYYMMDD format. Return it as event_date.
02ambiguous: Both slash readings succeed but give different dates. Return NULL for event_date.
03unambiguous: Exactly one slash reading succeeds, or both succeed and agree. Return that date.
04unreadable: No reading succeeds. Return NULL for event_date.
Keep both slash-reading columns even when an ISO reading determines the verdict. Return date values as DATE.
Ordering: Sort by event_id in ascending order.
Returnevent_idday_firstmonth_firstevent_dateverdict
About this problem
You practise reading dates that two systems wrote to different conventions, and – more importantly – recognising which values cannot be read at all without someone making a decision. Each value in feed_events is parsed as both day-first and month-first, and the pair of results decides whether the date is known, genuinely ambiguous, or not a date. The skill is resisting the urge to pick a format and move on.
Nearly everyone writes the day-first version of this query, and it is wrong in two directions at once. It converts 05/04/2026 to 5 April, which may be right or may be a month out, and it turns 05/13/2026 into NULL, throwing away a value that is perfectly readable the other way. The second reading is what turns a guess into evidence: when 13/05/2026 parses only one way the format is settled by the data itself, and when 03/03/2026 parses both ways to the same day there is nothing to settle. Only the values that parse both ways to different days are a real problem, and on this sample that is one row out of eight – which is the number the team actually asked for. The other thing worth noticing is that 31/02/2026 returns NULL rather than rolling forward into March. Some date parsers accept impossible dates and quietly normalise them, and a report built on one of those would show a day’s revenue landing on the wrong date with nothing to indicate it.
Read the lesson: Data Types & CAST (P13) →