Handling Clamped 9999 Timestamps

Problem

The BI Connector stores timestamps in the format yyyy-mm-dd hh:mm:ss.dddddd. Any source value at or beyond the year **9999** is clamped to 9999-12-01 before it lands in Redshift. This is the documented convention for **"no expiry" / "open-ended access"** — most commonly seen on enrollment due dates, license expirations, and content access windows that were set as effectively unlimited.

Treating these clamped values as real dates produces misleading results:

• DATEDIFF('day', CURRENT_DATE, duedate) returns ~2.9 million days.
• "Records expiring in the next year" reports incorrectly exclude them.
• AVG / MIN / MAX over timestamp columns are skewed.

Solution

-- Normalize clamped 9999 timestamps to NULL ("no expiry / open-ended")
SELECT
  uc.*,
  CASE
    WHEN uc.duedate >= '9999-01-01' THEN NULL
    ELSE uc.duedate
  END AS duedate_clean
FROM usercourses uc;

-- How many records are "open-ended" vs have a real value
SELECT
  SUM(CASE WHEN duedate >= '9999-01-01' THEN 1 ELSE 0 END) AS no_duedate_set,
  SUM(CASE WHEN duedate <  '9999-01-01' THEN 1 ELSE 0 END) AS duedate_set
FROM usercourses;

-- Enrollments actually due in the next 30 days
-- (excludes the clamped 9999 sentinel)
SELECT *
FROM usercourses
WHERE statusgroup = 'a'
  AND duedate < '9999-01-01'
  AND duedate BETWEEN CURRENT_DATE AND CURRENT_DATE + INTERVAL '30 days';

How it works

• >= '9999-01-01' catches the entire clamped range (the BI Connector documents the ceiling as 9999-12-01, but using >= '9999-01-01' is safer against future changes to the sentinel).
• Replacing with NULL makes downstream DATEDIFF / averages / BETWEEN filters behave correctly without each consumer having to remember the special case.

Variations

• **Different columns:** swap duedate for any open-ended timestamp on the table (e.g. expiresat on a license, accessendsat on userlicenses).
• **Different sentinel:** if you'd rather expose the sentinel as a sentinel (e.g. for an "is open-ended" BI dimension), return a boolean column instead: CASE WHEN duedate >= '9999-01-01' THEN TRUE ELSE FALSE END AS is_open_ended.