Storing ROC Dates in a Database: Do's and Don'ts

Store a real DATE column with the Gregorian value, computed once when the ROC-formatted data enters your system. Never store the ROC year, alone, as the source of truth — it is a display format, not a data type.

Do: store Gregorian, convert once

An ROC date and its Gregorian equivalent are the same date. There is no information in "115/07/29" that isn't in "2026-07-29" — the ROC form is purely a display convention. So the database should hold one canonical value, a real Gregorian DATE, and the ROC representation should be generated wherever it's needed for display, not stored as a second source of truth:

Column	issued_on DATE NOT NULL
Stored as	2026-07-29
Displayed as	115/07/29 (computed at render time: YEAR(issued_on) - 1911)

This is the same principle as storing timestamps in UTC and converting to a user's local timezone only for display — one canonical stored form, many derived display forms. Verify any converted value with the ROC year converter while you're building the migration.

Don't: store the ROC year as a bare integer or two-digit field

Three schema mistakes recur across systems built for Taiwanese data:

• A two-digit ROC year column. This is the exact cause of the Y1C bug: ROC year 100 (2011) truncates to 00, ROC 101 to 01, and so on, making the column ambiguous the moment it rolls over. If you must store an ROC year as its own field, zero-pad to at least three digits.
• An ROC year with no calendar-type marker, mixed into a column that sometimes holds Western years. A migration from an older system, or a form that let users pick either format, can leave a column where 87 means ROC 87 (1998) in one row and a typo'd two-digit Western year in another. Nothing in the number itself tells you which. Add an explicit calendar-type column rather than trying to infer it.
• Storing only the formatted string ("115/07/29") with no real date column behind it. This makes every range query, sort and date-arithmetic operation require re-parsing the string first, defeats indexing, and duplicates the conversion logic into every query that touches the column.

Handling pre-1912 and "before ROC" dates

A small number of records — old household registers, historical archives — predate the Republic of China and are conventionally written as "before ROC N" rather than a negative ROC year. Don't try to force these through the same roc_year + 1911 formula; store the Gregorian year directly (a DATE column has no trouble with 1895) and compute the "before ROC N" display form only if you actually need to show it. There is no ROC year 0, and there is no clean way to represent "before ROC" as a single signed integer without a documented convention that every consumer of the column has to know about.

Migrating an existing table safely

If you've inherited a table with an ROC-formatted text or integer column and need to add a real DATE column behind it:

1. Add the new DATE column as nullable, alongside the existing one — don't replace it in the same migration.
2. Backfill it with a batch job that runs the conversion once per row, logging any row that fails to parse or produces an ROC year of 0 or less, rather than silently skipping or defaulting it.
3. Reconcile the failures manually; a legacy table with years of accumulated data entry almost always has a handful of genuinely malformed rows, and a two-digit-truncated Y1C batch is common if the table predates 2011.
4. Only once the backfill is verified, switch reads over to the new column and drop or archive the old one.

Rushing straight to an in-place conversion, with no parallel column and no reconciliation step, is how a single ambiguous row becomes a silent data-corruption incident discovered months later.

Indexing and query performance

Once dates are stored as a real DATE/DATETIME column, ordinary indexing rules apply — a B-tree index on the date column supports range queries (WHERE issued_on BETWEEN ...) efficiently. If your application frequently queries "all records in ROC year 115," a computed or generated column for the ROC year (YEAR(issued_on) - 1911, indexed) is a reasonable addition, but it should always be derived from the canonical DATE column, never the other way round.

Frequently asked questions

Should I store ROC dates as a native DATE column or as text?

Always a native DATE (or DATETIME/TIMESTAMP) column holding the Gregorian value. A native date type gives you correct sorting, range queries, indexing and arithmetic for free; a text column of ROC-formatted strings gives you none of those without re-parsing on every query.

Is it ever correct to store the ROC year as its own integer column?

Only as a derived, denormalised convenience column alongside a real date column — never as the sole representation. If the ROC year is the only thing stored, every query needs to reconstruct month and day logic around it, and the column becomes ambiguous the moment anyone forgets which calendar it's in.

How many digits should an ROC year column use?

If you store an ROC year at all, use at least three digits with zero-padding (e.g. 099, 100, 115), never two. A two-digit field is exactly what caused the Y1C bug when ROC year 100 arrived in 2011 and truncated to 00.

How do I handle a mixed column where some rows are ROC and some are Western years?

Add an explicit calendar-type column rather than trying to infer it from the number. Inferring from digit count or magnitude works until it doesn't — a four-digit year that happens to start with a plausible ROC-adjacent number, or a legacy import that mixed formats, will silently misparse. An explicit flag removes the ambiguity permanently.

Should the ROC-to-Western conversion happen in the database or the application?

Convert once, at the boundary where the ROC-formatted data enters the system — during import or on write — and store the Gregorian value. Don't convert on every read in application code or in a view; that duplicates the conversion logic in multiple places and makes it easy for one copy to drift from the others.