Date Format Normalization Self-Correction Prompt

This prompt is designed for data engineers and ETL developers who need to programmatically repair malformed or ambiguous date strings during ingestion. The core job-to-be-done is to take a failed date parse, the original raw string, and the expected output format, and produce a reliably normalized value or a structured null with a clear failure reason. It is not a general-purpose date formatting tool; it is a self-correction step inside a retry loop, called after an initial parsing operation has already failed or produced an ambiguous result.

Use this prompt when your pipeline encounters date strings from heterogeneous sources—CSV files, APIs, user-generated spreadsheets, or legacy databases—where formats are inconsistent. It is particularly valuable for resolving ambiguity in formats like MM/DD/YYYY versus DD/MM/YYYY when the day value is 12 or less. The prompt should be wired into a data validation harness that catches DateTimeParseException or custom validation errors, constructs the prompt with the raw value, the expected format, and the error context, and then validates the model's output against a strict regex or date library before accepting the correction. Do not use this prompt for date arithmetic, timezone conversion, or locale-aware formatting; those are separate, deterministic operations that should be handled in application code after normalization.

Before deploying this prompt, define a retry budget—typically one or two attempts—and a clear escalation path. If the model returns a null with a parse-failure reason, log the original record to a dead-letter queue for manual review. If the model returns a date that still fails downstream validation, do not retry indefinitely; escalate immediately. The prompt is a recovery tool, not a guarantee, and its value comes from reducing the manual triage burden, not eliminating it entirely.

This prompt template is designed for ETL pipelines that ingest date and timestamp strings from heterogeneous sources. It takes a malformed or ambiguous date string and an expected output format, then returns a normalized value or a null with a parse-failure reason. The prompt includes explicit ambiguity detection for formats like MM/DD vs DD/MM, and it instructs the model to prefer returning a failure reason over guessing when the input is truly ambiguous.

textCopy
You are a date normalization function in an ETL pipeline. Your job is to parse a malformed or ambiguous date string and convert it to a strict output format.

INPUT_DATE: [INPUT_DATE]
TARGET_FORMAT: [TARGET_FORMAT]
SOURCE_HINT: [SOURCE_HINT]
AMBIGUITY_THRESHOLD: [AMBIGUITY_THRESHOLD]

RULES:
1. Attempt to parse INPUT_DATE into a valid date.
2. If the date is unambiguous, format it exactly as TARGET_FORMAT specifies.
3. If the date is ambiguous (e.g., '01/02/2023' could be Jan 2 or Feb 1), use SOURCE_HINT to resolve the ambiguity. SOURCE_HINT may be a locale code like 'en-US' (MM/DD/YYYY), 'en-GB' (DD/MM/YYYY), or an ISO 8601 preference.
4. If ambiguity cannot be resolved with confidence above AMBIGUITY_THRESHOLD (a float from 0.0 to 1.0), do not guess. Return a null date and a reason.
5. If the input cannot be parsed as any date, return a null date and a reason.

OUTPUT_SCHEMA:
{
  "normalized_date": "string | null",
  "input_was_ambiguous": boolean,
  "confidence": float,
  "failure_reason": "string | null"
}

Return ONLY the JSON object. No other text.

To adapt this template, replace the square-bracket placeholders with values from your pipeline context. [INPUT_DATE] is the raw string from your source system. [TARGET_FORMAT] should be a strftime-style or ISO 8601 format string (e.g., %Y-%m-%d or YYYY-MM-DD). [SOURCE_HINT] is critical for disambiguation—provide a locale, a known source system convention, or a column-level format preference. [AMBIGUITY_THRESHOLD] controls how aggressively the model resolves ambiguous dates; a value of 0.9 forces near-certainty, while 0.7 allows more permissive resolution. After copying the template, wire the output through a post-processing validator that confirms the normalized_date field matches [TARGET_FORMAT] before the record proceeds downstream. For high-volume pipelines, log every failure_reason to a dead-letter store for later analysis and schema evolution.

This prompt is designed to be called as a recovery step inside an ETL pipeline, not as a standalone utility. The primary integration point is immediately after a date-parsing operation fails against the target schema. The harness should catch the ValueError, ParseException, or custom DateFormatError, extract the offending string and the expected format, and construct the prompt call. Do not send every date through this prompt—only malformed or ambiguous values that fail a deterministic parser first. This keeps costs low and latency predictable while reserving the model for genuinely hard cases.

The harness must enforce a strict contract around the model's output. Expect a JSON object with normalized_date (a string in the target format or null) and parse_failure_reason (a string or null). Implement a post-processing validator that confirms the normalized_date field, if present, can be parsed by the same strict parser that originally failed. If the model returns a date that still fails parsing, log the failure, increment a retry counter, and re-invoke the prompt with the validator error appended to the [ERROR_CONTEXT]. Set a hard retry budget of 2 attempts; if both fail, write the record to a dead-letter queue with the original value, the model's last response, and a manual_review_required flag. For ambiguity detection (e.g., 01/02/2025), the harness should check the parse_failure_reason for keywords like ambiguous or multiple interpretations and route those records to a human review queue instead of silently choosing a format.

Model choice and latency matter here. This is a structured extraction task with low reasoning depth, so a fast model like claude-3-haiku or gpt-4o-mini is appropriate. Set temperature=0 to maximize determinism. For high-throughput pipelines, batch up to 20 failures into a single prompt call by providing a JSON array of [MALFORMED_DATES] and asking for an array of results, but ensure each output object includes the index of the original input to enable re-joining. Audit logging is non-negotiable: every invocation must log the input string, the target format, the model's raw response, the post-validation result, the retry count, and the final disposition (repaired, dead-lettered, or escalated). This log is essential for debugging format drift in upstream sources and for demonstrating data lineage to compliance reviewers. Wire these logs into your existing pipeline observability stack (e.g., Datadog, Grafana, or a custom etl_audit table). Never let the model write directly to your production tables—always pass its output through the validator and your own upsert logic.