ROC Dates in MySQL and MariaDB

MySQL and MariaDB have no ROC calendar type. For a string like 115/07/29, STR_TO_DATE(CONCAT(LEFT(col,3)+1911, SUBSTRING(col,4)), '%Y/%m/%d') returns a real DATE.

The conversion, in SQL

The safest pattern is to rebuild the string with the corrected Gregorian year and then let STR_TO_DATE parse the rest normally, rather than trying to compute year, month and day separately:

SELECT STR_TO_DATE(
  CONCAT(LEFT(roc_date_text, 3) + 1911, SUBSTRING(roc_date_text, 4)),
  '%Y/%m/%d'
) AS western_date
FROM staging_imports
WHERE roc_date_text = '115/07/29';
-- western_date: 2026-07-29

LEFT(roc_date_text, 3) + 1911 performs implicit string-to-integer conversion in MySQL, which is fine for well-formed input but will return 0 for anything that isn't numeric — validate the source format before trusting this in a batch import. Spot-check results against the ROC year converter.

Strict mode matters more than usual here

By default, older MySQL configurations tolerate invalid dates and silently store a zero date (0000-00-00) rather than raising an error. Combined with the ROC offset, this hides real bugs: a malformed row that should fail import instead becomes a suspicious-looking but "valid" row. Enable STRICT_TRANS_TABLES (part of the default SQL mode since MySQL 5.7) for any pipeline that imports ROC dates, so a bad month or day value raises an error you can catch, instead of a silent zero date you discover months later in a report.

Unpadded and compact formats

For 115/7/29 (unpadded month/day), STR_TO_DATE's %m and %d specifiers already accept one- or two-digit values, so the same query works once the year segment is isolated with SUBSTRING_INDEX instead of a fixed offset:

SELECT STR_TO_DATE(
  CONCAT(
    SUBSTRING_INDEX(roc_date_text, '/', 1) + 1911,
    '/', SUBSTRING(roc_date_text, LENGTH(SUBSTRING_INDEX(roc_date_text, '/', 1)) + 1)
  ),
  '%Y/%m/%d'
) AS western_date
FROM staging_imports
WHERE roc_date_text = '115/7/29';

For a compact 1150729 string with no separators, the fixed-width approach from the first example applies directly with '%Y%m%d' as the format string.

Displaying a stored date as an ROC string

MySQL has no format specifier that emits ROC years, so reverse the conversion manually with LPAD to keep the three-digit zero-padded form:

SELECT CONCAT(
  LPAD(YEAR(western_date) - 1911, 3, '0'),
  '/', DATE_FORMAT(western_date, '%m/%d')
) AS roc_date_text
FROM events;
-- '115/07/29'

Storage: DATE column, ROC text as a derived value

Store the Gregorian value in a real DATE or DATETIME column so range queries, joins and indexes behave normally. If you need to reproduce the original ROC-formatted string exactly — matching a printed form, for instance — keep it as a separate audit column derived from the DATE column, not the field you query against.

Edge cases specific to MySQL

Implicit string-to-number conversion hides bad input. LEFT(col,3) + 1911 on a non-numeric string returns 1911, not an error, in permissive SQL modes — a row with garbage in the year field can silently become a valid-looking 1911 date. Validate with a regex (col REGEXP '^[0-9]{3}/[0-9]{2}/[0-9]{2}$') before conversion.

There is no ROC year 0. A computed ROC year of 0 or negative from bad arithmetic is a bug, not a valid pre-1912 date; check for it explicitly rather than letting STR_TO_DATE construct whatever Gregorian year results.

Two-digit year columns truncate at ROC 100. See the Y1C write-up if you're auditing a legacy table for this pattern before migrating it.

Frequently asked questions

How do I convert a ROC date string to a MySQL DATE?

Rebuild the string with the Gregorian year and parse it: STR_TO_DATE(CONCAT(LEFT(col,3)+1911, SUBSTRING(col,4)), '%Y/%m/%d') for a 115/07/29-style column. MySQL's DATE type is always Gregorian, so the +1911 step has to happen before STR_TO_DATE sees the string.

Does MySQL or MariaDB support the ROC or Taiwan calendar natively?

No. Neither has a calendar type or locale setting that parses or displays ROC years. DATE_FORMAT, STR_TO_DATE and every date function operate purely in the Gregorian calendar, so ROC conversion is always application-level SQL, not a built-in feature.

Why does STR_TO_DATE fail silently on an ROC date string?

Because a format string of '%Y/%m/%d' applied to raw text like 115/07/29 parses 115 as a literal year, producing the date 0115-07-29 instead of an error, unless strict SQL mode is enabled. Always add 1911 to the year portion before calling STR_TO_DATE, and enable STRICT_TRANS_TABLES so genuinely invalid dates raise a warning or error instead of silently becoming zero dates.

How do I display a stored MySQL date as an ROC string?

Build it manually: CONCAT(LPAD(YEAR(col)-1911,3,'0'), '/', DATE_FORMAT(col,'%m/%d')). MySQL has no format specifier for ROC years, so the year has to be computed and padded separately from the month and day.

Should I store dates as MySQL DATE columns or as ROC-format text?

Store a real DATE or DATETIME column with the Gregorian value; it is what makes indexing, range queries and comparisons correct and fast. Keep an ROC-format text column only when you need to reproduce a source document exactly, and derive it from the DATE column rather than treating it as the source of truth.