Clinical Data Mappings

Partial dates and --DTC fields

Why padding a date-only source value with midnight invents precision, shown on a synthetic adverse-event onset.

The problem

Clinical dates often arrive incomplete: a year, a year-month, a date without a time, or a start with no end. SDTM-style timing variables (names that end in DTC, for “date/time character”) are commonly stored as ISO 8601 strings so that missing parts can stay missing. Warehouse types like TIMESTAMP do not have a “month only” state. If you cast first and format later, you have already invented a day or a time.

A working example

Synthetic AE onsets executed in DuckDB 2.0.0:

source id what the source knew illustrative –DTC string if you format the timestamp blindly
AE-001 2026-02-14 15:30:00 2026-02-14T15:30:00 2026-02-14T15:30:00 (same)
AE-002 2026-02-15 (date only) 2026-02-15 2026-02-15T00:00:00

The second column of the last row is the bug. The source never said midnight. A duration, a “time to onset,” or a “same-day as dose” flag computed from that midnight is a derived fiction.

A safer pattern (still illustrative):

FHIR date vs dateTime has the same distinction: a birthDate of 1985 is not 1985-01-01.

The gotcha

UTC conversion of a date-only value is another way to invent a time. “2026-02-15 in America/New_York” is not a defined instant. Do not shift date-only fields through a session time zone.

End dates that are partial when start dates are complete will break a naive end - start in SQL. That is usually a missing-data rule, not a subtraction.

When not to use this

Verification

The two synthetic onsets, and the midnight padding of AE-002, were produced in DuckDB 2.0.0. Official SDTM --DTC / ISO 8601 conventions should be read from the CDISC IG and timing guidance for your version; this page does not reproduce those tables.

Official docs