ROC Dates in SQL Server: Conversion Queries

SQL Server has no ROC or Taiwan calendar type. For a fixed-width string like 115/07/29, DATEFROMPARTS(LEFT(col,3)+1911, SUBSTRING(col,5,2), RIGHT(col,2)) returns a real DATE value.

The conversion, in T-SQL

DATEFROMPARTS is preferable to string concatenation plus CONVERT, because it takes integer year/month/day arguments directly and raises a clear error on an invalid combination rather than producing a date that silently rolled over:

SELECT DATEFROMPARTS(
  CAST(LEFT(roc_date_text, 3) AS INT) + 1911,
  CAST(SUBSTRING(roc_date_text, 5, 2) AS INT),
  CAST(RIGHT(roc_date_text, 2) AS INT)
) AS western_date
FROM staging.imported_rows
WHERE roc_date_text = '115/07/29';
-- western_date: 2026-07-29

Check any individual value against the ROC year converter while you're validating a new import.

Handling unpadded and compact formats

Not every source is a tidy fixed-width string. For 115/7/29 (unpadded month/day), use STRING_SPLIT with its ordinal column (SQL Server 2022+) and pivot the three parts back into DATEFROMPARTS:

SELECT DATEFROMPARTS(
  MAX(CASE WHEN ordinal = 1 THEN CAST(value AS INT) END) + 1911,
  MAX(CASE WHEN ordinal = 2 THEN CAST(value AS INT) END),
  MAX(CASE WHEN ordinal = 3 THEN CAST(value AS INT) END)
) AS western_date
FROM STRING_SPLIT('115/7/29', '/', 1);
-- western_date: 2026-07-29

On SQL Server versions before 2022, where STRING_SPLIT has no ordinal column, use CHARINDEX to locate each separator and SUBSTRING to pull out the three parts, the same approach as the compact-format example below but with variable-length segments.

For a compact 7-digit string like 1150729 with no separator, the fixed-width approach from the first example works directly — the year is always the first three characters, month the next two, day the last two.

Storing the value: date column, not text

Store a real DATE or DATETIME2 column holding the Gregorian value, computed once at insert or import time. Keep the original ROC text only where you need to reproduce a source document exactly — for example, matching a printed form during a reconciliation — and treat it as an audit field, not the value you query against. Comparing, sorting and range-filtering on the real date column is both correct and fast; doing the same on a text column of ROC strings requires re-deriving the year in every query, which defeats indexing.

Displaying a stored date back as an ROC string

SQL Server has no CONVERT style code or FORMAT culture string that emits ROC years, so the reverse direction is also manual. Build the string with FORMAT for the month/day and simple arithmetic for the year:

SELECT CONCAT(
  RIGHT('000' + CAST(YEAR(western_date) - 1911 AS VARCHAR(3)), 3),
  '/', FORMAT(western_date, 'MM/dd')
) AS roc_date_text
FROM dbo.events;
-- '115/07/29'

If you need this often, wrap it in a scalar function or, better for performance on large tables, a computed column so SQL Server only evaluates it once per row rather than once per query.

Edge cases specific to SQL Server

DATEFROMPARTS does not know ROC years exist. Pass it 1911 for the year and it will happily build a date in Gregorian 1911, because the function only ever sees the integer you hand it. Validate the source ROC year is 1 or greater before conversion; there is no ROC year 0, and a value of 0 or negative usually means bad input, not a legitimate pre-1912 record.

Two-digit year columns truncate at ROC 100. A legacy CHAR(6) column that only stores two digits for the year shows ROC 100 (2011) as 00, indistinguishable from a genuinely malformed row. See the Y1C write-up for how to detect this pattern in an existing table before you migrate it.

Server locale does not help here. Setting the SQL Server instance or session language to Chinese (Taiwan) changes month names and some formatting defaults, but it does not add ROC-year parsing to CAST, CONVERT or DATEFROMPARTS. There is no server setting that makes these functions ROC-aware.

Frequently asked questions

How do I convert an ROC date string to a SQL Server DATE?

Use DATEFROMPARTS with the ROC year plus 1911: DATEFROMPARTS(LEFT(col,3)+1911, SUBSTRING(col,5,2), RIGHT(col,2)) for a fixed-width 115/07/29-style string. DATEFROMPARTS returns NULL-safe errors for invalid dates rather than silently wrapping, which is preferable to string concatenation and CONVERT.

Does SQL Server have a built-in Taiwan or ROC calendar?

No. SQL Server's DATE, DATETIME2 and related types are always Gregorian, and there is no CONVERT style code or CULTURE setting that produces ROC years. Every conversion has to be written explicitly in T-SQL, as shown on this page.

Should I store the ROC year or the converted Gregorian date in the table?

Store a real DATE or DATETIME2 column with the Gregorian value. Keep the original ROC-formatted string only if you must reproduce it exactly for a document or an audit trail, and derive the ROC display string from the DATE column with a computed column or a view, not the other way round.

How do I display a stored date as an ROC-format string in a query?

Build the string manually: CONCAT(RIGHT('000' + CAST(YEAR(col)-1911 AS VARCHAR(3)),3), '/', FORMAT(col,'MM/dd')). SQL Server has no CONVERT style or FORMAT culture string that emits ROC years directly.

What happens if I run DATEFROMPARTS on an ROC year of 0?

DATEFROMPARTS(1911, m, d) will happily construct 1 January to 31 December 1911, because SQL Server has no concept of an ROC year at all — it only sees the Gregorian year you pass in. Validate that the source ROC year is 1 or greater before conversion; a 0 or negative ROC year is a data problem, not a valid pre-1912 date.