Date string format mismatch — the source system (API, flat file, legacy database) delivers dates as 'MM/DD/YYYY' or 'DD-MM-YYYY' while MySQL strictly requires 'YYYY-MM-DD'. A single misformatted value in a batch causes the entire INSERT or LOAD to fail when STRICT_TRANS_TABLES is active.. Zero-date placeholder ('0000-00-00' or '0000-00-00 00:00:00') inserted into a column while NO_ZERO_DATE or NO_ZERO_IN_DATE SQL mode is enabled. Legacy systems commonly use zero-dates as a substitute for NULL; MySQL 8.0 has these modes enabled by default in STRICT_TRANS_TABLES.. TIMESTAMP column overflow — values before 1970-01-01 00:00:01 UTC or after 2038-01-19 03:14:07 UTC are out of range for TIMESTAMP. ETL pipelines that backfill historical records or project future dates into a TIMESTAMP column will fail on out-of-range rows.. ADF Sink passes an empty string ('') or a whitespace-only string to a NOT NULL DATETIME column when no explicit null-handling mapping is configured. An empty string is not a valid datetime literal and MySQL rejects it immediately.. Timezone offset embedded in the source string — values like '2024-03-15T10:00:00+02:00' or '2024-03-15T10:00:00Z' (ISO 8601 with timezone) are not valid MySQL DATETIME literals. MySQL DATETIME has no timezone component; the offset must be stripped or converted before insertion.. dbt incremental model passes a Python/Jinja-rendered date string with microsecond precision beyond six digits, or with a 'T' separator instead of a space — both are rejected by MySQL's DATETIME parser in strict mode.. Daylight saving time gap — when a TIMESTAMP value falls in the skipped hour during a DST transition (e.g., 2024-03-31 02:30:00 in a CET timezone), MySQL cannot resolve the ambiguous local time and raises 1292.

Step 1: Identify the offending value. Run `SELECT * FROM your_table WHERE STR_TO_DATE(your_col, '%Y-%m-%d %H:%i:%s') IS NULL AND your_col IS NOT NULL LIMIT 20;` — or check the ADF activity output JSON for 'errorMessage' which includes the exact rejected value and row number.. Step 2: Normalize date format in ADF using a Derived Column transformation before the Sink. Expression: `toDate(source_date_col, 'MM/dd/yyyy')` for date-only, or `toTimestamp(source_dt_col, 'MM/dd/yyyy HH:mm:ss')` for datetime. Map the derived column to the target column in the Column Mapping tab.. Step 3: Convert format in MySQL directly using STR_TO_DATE when loading via INSERT or stored procedure: `INSERT INTO target (dt_col) VALUES (STR_TO_DATE('31/12/2024 14:30:00', '%d/%m/%Y %H:%i:%s'));` — returns NULL (not an error) if the string is unparseable, so combine with a NOT NULL constraint check upstream.. Step 4: Replace zero-date placeholders before insertion. In the source query or a staging table: `SELECT IF(dt_col = '0000-00-00 00:00:00', NULL, dt_col) AS dt_col FROM source_table;` — or in an ADF Derived Column: `iif(equals(source_dt_col, '0000-00-00 00:00:00'), null(), source_dt_col)`.. Step 5: Change TIMESTAMP columns that need dates outside 1970–2038 to DATETIME: `ALTER TABLE your_table MODIFY COLUMN dt_col DATETIME NULL;` — DATETIME supports '1000-01-01 00:00:00' through '9999-12-31 23:59:59' and is not affected by Unix timestamp overflow.. Step 6: Strip timezone offsets from ISO 8601 strings in dbt or ADF. In dbt (MySQL adapter): `CONVERT_TZ(STR_TO_DATE(LEFT(source_col, 19), '%Y-%m-%dT%H:%i:%s'), '+02:00', '+00:00')` — adjust the source offset to match your pipeline's input timezone.. Step 7: If the error occurs in bulk loads and some bad rows must pass, temporarily disable strict mode for the session: `SET SESSION sql_mode = REPLACE(@@SESSION.sql_mode, 'STRICT_TRANS_TABLES', '');` — MySQL will then coerce invalid values to '0000-00-00' or NULL depending on the column definition. Re-enable strict mode immediately after and log the coerced rows for remediation.

Medium severitydata integrity

MySQL Error:
1292

Impact

Every row containing the offending date value is rejected — in strict mode the entire INSERT batch fails, not just the bad row, so a single format anomaly in a 100k-row ADF copy activity fails the full pipeline run and leaves the target table in a partial or stale state. Downstream dbt models reading from that table will then operate on yesterday's data without warning.

In STRICT_TRANS_TABLES mode (MySQL 8.0 default) a single invalid datetime causes the entire multi-row INSERT to roll back — partial writes do not occur, but the pipeline activity is marked Failed and no data is loaded for that run. ADF does not retry row-level data errors by default; the pipeline run stays in Failed state until the next scheduled trigger or a manual rerun. If the pipeline feeds a daily reporting model, the downstream table remains stale for 24 hours and any SLA on freshness is breached. In non-strict mode MySQL silently coerces the value, which is worse: data lands corrupted without an alert.

What does this error mean?

MySQL error 1292 is raised when a value being written to a DATE, DATETIME, or TIMESTAMP column cannot be parsed or falls outside the column's valid range. In a data pipeline this surfaces as a row-level rejection: ADF marks the activity as Failed with error code UserErrorDatabaseOperationFailed, dbt raises a database error during a model run, or a LOAD DATA statement silently truncates rows depending on strict mode. The engineer sees the MySQL message 'Incorrect datetime value: X for column Y at row Z' in the pipeline logs — often after a source system changes its date format or introduces zero-date placeholders.

Common causes

1Date string format mismatch — the source system (API, flat file, legacy database) delivers dates as 'MM/DD/YYYY' or 'DD-MM-YYYY' while MySQL strictly requires 'YYYY-MM-DD'. A single misformatted value in a batch causes the entire INSERT or LOAD to fail when STRICT_TRANS_TABLES is active.
2Zero-date placeholder ('0000-00-00' or '0000-00-00 00:00:00') inserted into a column while NO_ZERO_DATE or NO_ZERO_IN_DATE SQL mode is enabled. Legacy systems commonly use zero-dates as a substitute for NULL; MySQL 8.0 has these modes enabled by default in STRICT_TRANS_TABLES.
3TIMESTAMP column overflow — values before 1970-01-01 00:00:01 UTC or after 2038-01-19 03:14:07 UTC are out of range for TIMESTAMP. ETL pipelines that backfill historical records or project future dates into a TIMESTAMP column will fail on out-of-range rows.
4ADF Sink passes an empty string ('') or a whitespace-only string to a NOT NULL DATETIME column when no explicit null-handling mapping is configured. An empty string is not a valid datetime literal and MySQL rejects it immediately.
5Timezone offset embedded in the source string — values like '2024-03-15T10:00:00+02:00' or '2024-03-15T10:00:00Z' (ISO 8601 with timezone) are not valid MySQL DATETIME literals. MySQL DATETIME has no timezone component; the offset must be stripped or converted before insertion.
6dbt incremental model passes a Python/Jinja-rendered date string with microsecond precision beyond six digits, or with a 'T' separator instead of a space — both are rejected by MySQL's DATETIME parser in strict mode.
7Daylight saving time gap — when a TIMESTAMP value falls in the skipped hour during a DST transition (e.g., 2024-03-31 02:30:00 in a CET timezone), MySQL cannot resolve the ambiguous local time and raises 1292.

How to fix it

1Step 1: Identify the offending value. Run `SELECT * FROM your_table WHERE STR_TO_DATE(your_col, '%Y-%m-%d %H:%i:%s') IS NULL AND your_col IS NOT NULL LIMIT 20;` — or check the ADF activity output JSON for 'errorMessage' which includes the exact rejected value and row number.
2Step 2: Normalize date format in ADF using a Derived Column transformation before the Sink. Expression: `toDate(source_date_col, 'MM/dd/yyyy')` for date-only, or `toTimestamp(source_dt_col, 'MM/dd/yyyy HH:mm:ss')` for datetime. Map the derived column to the target column in the Column Mapping tab.
3Step 3: Convert format in MySQL directly using STR_TO_DATE when loading via INSERT or stored procedure: `INSERT INTO target (dt_col) VALUES (STR_TO_DATE('31/12/2024 14:30:00', '%d/%m/%Y %H:%i:%s'));` — returns NULL (not an error) if the string is unparseable, so combine with a NOT NULL constraint check upstream.
4Step 4: Replace zero-date placeholders before insertion. In the source query or a staging table: `SELECT IF(dt_col = '0000-00-00 00:00:00', NULL, dt_col) AS dt_col FROM source_table;` — or in an ADF Derived Column: `iif(equals(source_dt_col, '0000-00-00 00:00:00'), null(), source_dt_col)`.
5Step 5: Change TIMESTAMP columns that need dates outside 1970–2038 to DATETIME: `ALTER TABLE your_table MODIFY COLUMN dt_col DATETIME NULL;` — DATETIME supports '1000-01-01 00:00:00' through '9999-12-31 23:59:59' and is not affected by Unix timestamp overflow.
6Step 6: Strip timezone offsets from ISO 8601 strings in dbt or ADF. In dbt (MySQL adapter): `CONVERT_TZ(STR_TO_DATE(LEFT(source_col, 19), '%Y-%m-%dT%H:%i:%s'), '+02:00', '+00:00')` — adjust the source offset to match your pipeline's input timezone.
7Step 7: If the error occurs in bulk loads and some bad rows must pass, temporarily disable strict mode for the session: `SET SESSION sql_mode = REPLACE(@@SESSION.sql_mode, 'STRICT_TRANS_TABLES', '');` — MySQL will then coerce invalid values to '0000-00-00' or NULL depending on the column definition. Re-enable strict mode immediately after and log the coerced rows for remediation.

Example log output

com.mysql.jdbc.exceptions.jdbc4.MySQLDataException: Incorrect datetime value: '03/15/2024' for column 'created_at' at row 1
[ADF] ErrorCode=UserErrorDatabaseOperationFailed, Message=Failure happened on 'Sink' side. ErrorCode=UserErrorDatabaseOperationFailed,Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException
[dbt] Database Error in model stg_orders (models/staging/stg_orders.sql): Incorrect datetime value: '' for column 'updated_at' at row 847

Frequently asked questions

MySQL 1292 fix — how do I find which rows are causing the error?

Run `SELECT your_col, COUNT(*) FROM staging_table WHERE STR_TO_DATE(your_col, '%Y-%m-%d %H:%i:%s') IS NULL AND your_col IS NOT NULL GROUP BY your_col;` against the staging or source table to list every distinct value MySQL cannot parse. For ADF, enable logging on the Copy Activity (Settings → Enable logging) and check the 'skipped rows' log file in your storage account — it records the exact row and column value that triggered the error.

MySQL 1292 zero date — how do I disable the error for '0000-00-00'?

Remove NO_ZERO_DATE from the session SQL mode: `SET SESSION sql_mode = REPLACE(@@SESSION.sql_mode, 'NO_ZERO_DATE,', '');` — this allows zero-dates for the session. For a permanent change per connection in ADF, add `sessionInit` in the linked service advanced settings: `SET sql_mode='ALLOW_INVALID_DATES';`. The cleaner long-term fix is to replace zero-dates with NULL in the source query before they reach MySQL.

MySQL 1292 TIMESTAMP vs DATETIME — which should I use in a pipeline?

Use DATETIME for any column that may receive dates before 1970 or after 2038-01-19, which is common in financial, historical, or forecasting pipelines. TIMESTAMP stores a UTC Unix integer and auto-converts to the server timezone on read, which can introduce subtle DST bugs across regions. DATETIME stores the literal value with no timezone conversion — more predictable in ETL contexts where the source timezone is already normalized.

MySQL 1292 ADF retry — will ADF automatically retry and succeed?

No. Error 1292 is a data-content error, not a transient connectivity error. ADF's built-in retry policy (default: 0 retries) will not help because the same data is sent again and MySQL rejects it again. You must fix the upstream data transformation in the Copy Activity's source query or Derived Column transformation before retriggering the pipeline run.

Source · dev.mysql.com/doc/mysql-errors/8.0/en/server-error-reference.html