The date bug that only misfires when the day is 13 or higher

A week spent cleaning 290 booking records from Safari Connect, a Nairobi bus platform, with 21 columns and 23 catalogued data problems, produced five SQL bugs that ran to completion and returned plausible output ^[1][2]. One of them removed five completed bookings and KES 3,840 of revenue from every downstream total without raising an error or a warning ^[10].

The mechanism deserves attention because the code is the code most people would write. The departure_date column held three formats: 2024-09-15, 15/09/2024, and 09-25-2024 ^[4]. Two are ambiguous. As the author notes, 01-18-2024 can only be MM-DD-YYYY because there is no month 18, while 04-10-2024 could be read either way ^[5]. The supplied guide resolved this by inspecting the second component and treating the string as month-first when that value exceeded 12 ^[6].

That test can only be true when the day is 13 or higher ^[7]. Five rows had days between 1 and 12, so the UPDATE never touched them ^[8]. The next step inserted from staging into the target table with a WHERE clause matching the ISO pattern, and those five rows failed the match and were dropped ^[9]. That is 1.7 percent of the file ^[2], at an average of KES 768 per lost booking ^[1].

The tolerance check was the second failure. The guide's expected row count was written as "~280+" ^[11], which leaves ten rows of slack against a 290-row input, twice the number actually lost ^[3]. A spot check does not catch this either, because the broken subset is defined by a value in the data rather than by position in the file ^[7][8].

The recommended fix is to classify on shape rather than infer from values, using an anchored pattern such as '^\d{2}-\d{2}-\d{4}$' ^[12]. Because anchored patterns are mutually exclusive, every row can be bucketed and counted with a CASE expression before anything is modified ^[13]. The important bucket is UNRECOGNISED: if it is non-zero, stop ^[14]. The general form of the lesson, in the author's words, is that WHERE clauses inside an INSERT ... SELECT are the quietest place data disappears, so count what you are about to lose before you lose it ^[15].

The other four bugs share the shape. A month-over-month growth query with a 100.0 multiplier and a NULLIF guard against divide-by-zero returned a column of zeros ^[16]; multiplication and division have equal precedence and evaluate left to right, so the integer division truncated to zero before the decimal ever entered the expression ^[17]. Moving the constant ahead of the divisor, as (a - b) * 100.0 / c, fixes it ^[18]. A column of round zeros with no error is the signature ^[19].

The reporting layer had a view filtered to booking_status = 'Completed' ^[20]. One of the six business questions asked for the cancellation rate and the cost of cancellations, and against that view the answer is 0 percent and KES 0 ^[21]. The fix was to expose all bookings and split the money into realised and lost columns with CASE expressions ^[22]. A view encodes an assumption about which rows matter, and that assumption is invisible to whoever queries it later ^[23]. The ranking query showed the same class of problem from the other direction: eight drivers, nine rows, from a CTE grouped by driver_name and vehicle_type ^[24].

Worth watching in your own pipelines: whether any staging-to-production insert filters rows without logging the delta, and whether your row-count assertions are written as ranges loose enough to absorb a real loss.