---
title: "1900-01-01 Is Not a Date: The Zero-Datetime That Corrupts Every Migration"
canonical: https://dxdev.com/blog/1900-01-01-is-not-a-date-zero-datetime-migration/
datePublished: 2026-01-27
---
A blank date in old SQL Server data does not always come back as NULL. An empty string or a zero cast to `datetime` comes back as `1900-01-01`. Copy that row verbatim into a fresh table and you've just told your app that every undated game was played on New Year's Day, 1900.

I hit this in the worst possible place: a recovery engine. A customer's entire sports site had been deleted, a large multi-division basketball league, and the only way back was an application-level copy that resurrects every row from a dated backup database into live production. When you're inserting thousands of rows into prod, "off by one date column" isn't a cosmetic bug. It's a corrupted league you now have to recover from your recovery, which is the one job a recovery engine exists to never create.

## Where the zero-datetime comes from

In this copy path, an empty string or `0` cast to `datetime` lands on `1900-01-01`, turning an unset value into a sentinel. The date fields accumulated these forms of "no date here":

- actual `NULL`
- the literal string `"null"` (because something stringified it on the way in)
- `"undefined"` (same story, different layer)
- empty string
- and the SQL Server zero-datetime, `1900-01-01`

The first time I ran the recovery copy, it passed all of these straight through. The blank-string ones were obvious in testing. The `1900-01-01` ones were not, because `1900-01-01` *looks* like a real date. It serializes cleanly, it inserts without error, and it sails through any check that's only asking "is this a valid date." It is a valid date. It's just a lie.

## Why copying it verbatim is the trap

The recovery engine works by copying columns declared in hand-written lists, one list of string columns and one of numeric columns per table. A date column rides along as a string. The naive path is: read the value, hand it to the insert, move on. For a games table that means every undated game arrives in the new account stamped `1900-01-01`.

Now think about what reads that column downstream. Schedules sort by date, "Upcoming games" filters on it, season views bucket by it. An undated game that used to be invisible (because the date was blank) suddenly has the *earliest possible* date in the system, so it sorts to the very top of everything and shows up as the oldest game on record, supposedly played in 1900. Multiply by a league that size and you don't have one weird row. You have a schedule view that's wrong for the whole org, and a customer who notices.

The deeper problem is that `1900-01-01` defeats the obvious guard. If you write `if (!value) skip`, the zero-datetime is truthy, it's a non-empty string, so it slips through. You have to recognize the specific sentinel.

## The fix, part one: an IsNullDateValue check

The first half of the fix is a single predicate that treats every "no date here" form as one thing. Null out the obvious cases first, trim, lowercase the string comparisons, and collapse the whole set to true:

```javascript
function IsNullDateValue(raw) {
  if (raw === null || raw === undefined || raw === "") return true;
  var s = String(raw).replace(/^\s+|\s+$/g, "");
  if (!s || s.toLowerCase() === "null" || s.toLowerCase() === "undefined") return true;
  // SQL Server "zero" datetime and legacy blank dates often appear as 1900-01-01
  if (/^1900-01-01(\s|$)/i.test(s)) return true;
  return false;
}
```

The regex is the load-bearing line: `/^1900-01-01(\s|$)/i`. It matches the sentinel at the start of the string and then requires either whitespace or the end of the string. That makes both `1900-01-01` and `1900-01-01 00:00:00` read as nullish without matching a later date string.

Blank or `1900-01-01` must not be inserted as a date. The column gets omitted from the INSERT so the schema can apply its existing NULL or default behavior.

## The fix, part two: omit the column, don't blank it

This is the half that actually made "stay NULL" work.

Knowing a value *should* be null is not the same as getting a null into the row. If your copy layer builds an INSERT from a column list, the easy mistake is to substitute an empty string for the nullish value and insert that. Now you've traded `1900-01-01` for `''`. On a `datetime` column that empty string casts right back to, you guessed it, `1900-01-01`. You went in a circle. And on a column with a database default or a NOT NULL constraint, inserting a blank is exactly how you discover that constraint the hard way, mid-recovery, on production.

What you want is for the column to be *absent from the INSERT entirely*, so the database applies its own NULL or its own default. The data model already knows what an unset date should be. Let it decide.

The way I made that expressible: the generic copy loop has a per-string hook that returns the value to insert. I taught it to return `undefined`, and taught the loop to read `undefined` as "skip this column":

```javascript
var out = handlers.onString(field, val, rawVal);
if (out === undefined) continue;   // omit column so DB inserts NULL (e.g. null/1900 dates)
```

So the date handler runs `IsNullDateValue` against the raw value, and on a hit returns `undefined` instead of a string. The column drops out of the column list for that row. The INSERT goes out without it. The database fills in NULL or whatever default the schema defines, which is precisely the behavior an undated game had before anyone touched it.

The distinction matters more than it looks: "this value is empty" and "this column should not be in the statement" are different operations, and most copy layers can only express the first. If yours can only ever put *something* in every column, you cannot faithfully reproduce a row that had a column unset. Build "omit this column" as an explicit return value your copy layer can produce.

## A sibling bug that rode along

While auditing the games copy lists for the date issue, I found the inverse problem: the force-win flag columns were missing from several sports' numeric column lists. The forfeit columns were copied in most lists, but the force-win pair was left out. So on those sports a recovered force-win silently dropped to NULL: the column was never selected, so nothing carried the value over. The fix added the missing force-win columns alongside the forfeit flags across the games lists, basketball, baseball, football, hockey, and the rest.

There was a second half to it that the date bug taught me. For these four game-result flags, NULL is the wrong default in the other direction: an unset forfeit or force-win should be `0`, not NULL. So a small sentinel map forces the value to `0` when the source is null or empty, instead of omitting the column. Dates want absence. These flags want an explicit zero.

The copy loop needs three explicit outcomes: `undefined` omits a nullish date, `0` preserves an unset forfeit or force-win, and a normal value stays in the column list. Anything less loses the distinction the recovery depends on.

## Related

- [I wrote a 1,477-line site-recovery engine in Classic ASP: copy first, remap IDs second](site-recovery-engine-copy-first-remap-second): the recovery engine this migration logic lives inside
- [The OFFSET_ID trick: copying rows whose foreign keys you don't know yet](offset-id-trick-remap-foreign-keys-restore): how foreign keys are handled safely during the same copy pass
- [7 games became 14: making a bulk insert idempotent with an origin key](idempotent-bulk-insert-origin-key-double-games): avoiding double-inserts when the same recovery runs twice
- [From 3.5 million round-trips to hundreds: batching a remap with temp tables](batch-remap-temp-tables-3.5-million-round-trips): the performance fix for the ID-remap step that follows this copy pass
- [The One-Shot Data-Repair Script as a First-Class Artifact](one-shot-data-repair-script-as-first-class-artifact): treating recovery scripts with the same rigor as production code
- [A code-first schema audit: keeping a 1,477-line ASP file in sync with a 14,536-column DB](code-first-schema-audit-keep-copy-lists-honest): keeping copy lists honest so sentinel values don't sneak through