Your Dates Are 543 Years Off, and Every Row Loaded Successfully

Mostafa Dhouib·7 min read

Your Dates Are 543 Years Off, and Every Row Loaded Successfully

The short answer. There is a category of migration failure that produces no error of any kind, because the output is structurally valid. A date stored in a calendar whose year numbering differs from the Gregorian one, read as though it were Gregorian, shifts by a fixed offset. In one common case that offset is 543 years. The result is still a date. It parses, it validates, it loads, it sorts, and it is wrong by five centuries. The same shape covers text through the wrong code page, numeric separators, and timestamps stored without a zone. None of them is caught by row counts, null rates or schema validation, so a migration plan has to test for them by name.

Migration testing usually checks that the data arrived. Row counts match, required fields are populated, foreign keys resolve, nothing violates the schema.

All of those pass in the failures below, which is the entire problem. The data arrived. It is just not the same data.

The calendar offset

Some systems store dates using a calendar whose year numbering differs from the Gregorian one. A common case runs 543 years ahead, so a Gregorian year 2026 is stored as 2569.

Read those values as Gregorian during a migration and every date moves by 543 years. A customer's registration date, their contract start, their birth date, all shifted into the future by five centuries.

Nothing rejects it. 2569 is a perfectly valid year. The date parses, it fits the column type, it sorts in the same relative order as the originals, and every integrity check passes.

The relative ordering is what makes this survive testing. Records still appear in the right sequence, so a spot check of "is the newest record the newest" comes back correct. Age calculations are absurd and nobody computes an age during a migration test. The failure surfaces weeks later in a report, by which point a great deal has been built on top.

Text through the wrong code page

The same shape with characters. A legacy system stores text in a single-byte code page. The migration assumes a different encoding, commonly a modern multi-byte one.

Bytes that are valid in both encodings pass through unchanged, which covers most Latin characters, so a sample of English-language records looks perfect. Bytes that mean different things in each silently become different characters. Bytes that are invalid in the target may be replaced with a substitution character, which is a visible failure and therefore the lucky case.

What makes this nastier than the calendar problem is that it is partial. Most of the data is fine. The corruption is concentrated in exactly the records that use the character set the legacy encoding existed to support, which are usually the most important records in that market.

Row counts match. String lengths are close enough that nothing looks anomalous. And a reviewer who does not read the language cannot tell corrupted text from correct text by looking.

The rest of the family

Numeric separators. A decimal comma read as a thousands separator turns 1,5 into 15, a factor of ten on every affected value. Both are valid numbers.

Timestamps without a zone. A naive timestamp interpreted in the wrong zone shifts by hours. Every value is still a valid timestamp, and the shift is small enough to look like a plausible business variation rather than a defect.

Ambiguous date formats. Day-first versus month-first is unambiguous for the twelfth of any month onwards and silently wrong for the first eleven days. So roughly a third of your dates are wrong and two thirds are right, which is the worst possible mix for detection by sampling.

Every one of these produces structurally valid output. That is the defining property of the class.

How to test for them

The common feature is that validity checks cannot see them, so the tests have to be semantic rather than structural.

Range assertions with business meaning. No registration date should be in the future. No birth date should imply an age over 120. These are trivial to write and they catch the calendar offset immediately, which no schema check does.

Round-trip a known set. Take a sample of records, migrate them, and compare field by field against the source, not aggregates against aggregates. A hundred records compared properly finds more than a million compared by count.

Sample from the awkward records deliberately. Not random rows. Rows containing non-Latin characters, rows from the earliest and latest periods, rows with the largest and smallest numeric values, rows near month boundaries. The failures concentrate there and random sampling under-weights them.

Have the data read by someone who reads the language. For text encoding there is no substitute. A reviewer who cannot read the content cannot distinguish corruption from correctness.

Assert distributions, not just presence. The distribution of dates by year, of string lengths, of value magnitudes. A 543-year shift is invisible in a null-rate check and unmissable in a histogram of years.

Where this belongs in the plan

In the first gate, against a copy, before anything depends on the data having moved correctly.

This is the same argument as inventorying undocumented business rules before a replacement: the point of a first gate is to find what you do not know while finding it is still cheap. Locale traps belong there because they are discoverable by inspection, they are catastrophic when missed, and they cost almost nothing to check once somebody has named them.

The naming is the hard part. Each of these failures is obvious in retrospect and invisible if nobody thought of it, which makes a checklist worth more than cleverness here.

The honest limit

A checklist covers the traps on it. A system with a locale convention nobody on the team has encountered is not protected by a list of the ones they have, and the specific offsets and encodings vary by region and by vintage.

What generalises is the test: does this failure produce structurally valid output? If yes, no amount of schema validation, row counting or integrity checking will find it, and you need a semantic assertion written specifically for it. That question is worth asking of every field in a migration, and the answer is affirmative more often than people expect.

FAQ

Why did my migrated dates shift by 543 years? Because the source stored them in a calendar whose year numbering runs 543 years ahead of the Gregorian one, and the migration read them as Gregorian. The result is still a valid date, so it parses, validates, loads and sorts correctly while being wrong by five centuries.

Why don't schema checks catch locale migration errors? Because the output is structurally valid. A shifted year is a year, a mis-decoded character is a character, and a misread decimal separator is a number. Schema validation, row counts, null rates and foreign-key checks all pass, since nothing about the shape of the data is wrong.

How do I detect character corruption from a legacy code page? Sample deliberately from records containing non-Latin characters rather than randomly, compare field by field against the source, and have the result read by someone who reads the language. Corruption is partial and concentrated in exactly the records the legacy encoding existed to support, so random sampling under-weights it.

What tests actually find this class of migration bug? Semantic assertions rather than structural ones. Range checks with business meaning, such as no future registration dates and no implied ages over 120. Field-by-field round trips on a sample. Distribution assertions, since a 543-year shift is invisible in a null check and unmissable in a histogram of years.

Why is an ambiguous day-first or month-first date format especially hard to catch? Because it is unambiguous from the twelfth of any month onwards and silently wrong for the first eleven days, so roughly a third of dates are wrong and two thirds are right. That mix defeats detection by sampling, since most sampled records look correct.

When should locale traps be checked in a migration? In the first gate, against a copy, before anything is built that depends on the data having moved correctly. They are discoverable by inspection, catastrophic when missed, and nearly free to check once somebody has named them, which makes a checklist more valuable than cleverness.