Partial dates and --DTC fields
Synthetic data. Not submission-ready. Not a copy of official IG mapping tables. Licensed vocabularies are not republished here.
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):
- Keep a typed
DATEorTIMESTAMPonly when the source precision fills that type. - Also keep a character –DTC (or a precision flag) that preserves the original completeness:
2026,2026-02,2026-02-15, or2026-02-15T15:30:00. - Do not fill unknown parts with
01,12, or00:00:00in the official character field. If an analysis needs an imputed date, that imputation belongs in a documented ADaM derivation, not in the tabulation timing field.
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
- Do not invent an ISO string format of your own if your official IG or company standard already specifies the allowed patterns.
- Do not store only a timestamp and hope a comment column will remember the precision.
- Do not “complete” privacy-redacted birth dates.
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
- CDISC SDTM
- CDISC Library
- ISO 8601 (the date/time representation standard ISO publishes; the SDTM-specific profile is in CDISC materials)