Date string format mismatch — source uses 'MM/DD/YYYY' but MySQL expects 'YYYY-MM-DD'. Datetime value is zero ('0000-00-00 00:00:00') and NO_ZERO_DATE SQL mode is enabled. Datetime is outside MySQL TIMESTAMP range (1970-01-01 to 2038-01-19). ADF or dbt passes a NULL or empty string to a NOT NULL DATETIME column

Step 1: Standardize date format in ADF using a Derived Column transformation: `toDate(source_date, 'MM/dd/yyyy')` converted to 'yyyy-MM-dd'.. Step 2: Use STR_TO_DATE in MySQL for format conversion: `STR_TO_DATE('31/12/2024', '%d/%m/%Y')`. Step 3: Replace zero dates with NULL if using NO_ZERO_DATE mode: `UPDATE your_table SET dt_col=NULL WHERE dt_col='0000-00-00 00:00:00';`. Step 4: Use DATETIME instead of TIMESTAMP for dates outside the 2038 limit.

Medium severitydata integrity

Power BI Refresh Error:
1292

What does this error mean?

A DATETIME or TIMESTAMP value being inserted does not conform to MySQL's expected format or is out of the valid range.

Common causes

1Date string format mismatch — source uses 'MM/DD/YYYY' but MySQL expects 'YYYY-MM-DD'
2Datetime value is zero ('0000-00-00 00:00:00') and NO_ZERO_DATE SQL mode is enabled
3Datetime is outside MySQL TIMESTAMP range (1970-01-01 to 2038-01-19)
4ADF or dbt passes a NULL or empty string to a NOT NULL DATETIME column

How to fix it

1Step 1: Standardize date format in ADF using a Derived Column transformation: `toDate(source_date, 'MM/dd/yyyy')` converted to 'yyyy-MM-dd'.
2Step 2: Use STR_TO_DATE in MySQL for format conversion: `STR_TO_DATE('31/12/2024', '%d/%m/%Y')`
3Step 3: Replace zero dates with NULL if using NO_ZERO_DATE mode: `UPDATE your_table SET dt_col=NULL WHERE dt_col='0000-00-00 00:00:00';`
4Step 4: Use DATETIME instead of TIMESTAMP for dates outside the 2038 limit.

Frequently asked questions

What datetime format does MySQL expect?

MySQL expects 'YYYY-MM-DD' for DATE and 'YYYY-MM-DD HH:MM:SS' for DATETIME. Use STR_TO_DATE() to convert other formats.

What is the MySQL TIMESTAMP year 2038 problem?

TIMESTAMP columns store values as 32-bit Unix timestamps, which overflow on 2038-01-19. Use DATETIME columns for dates beyond 2038 — they support up to year 9999.

Can MetricSign detect when a date format change in source data breaks a pipeline?

Yes — MetricSign captures ADF pipeline failures with the MySQL error code, so you are alerted immediately when new data format issues appear.

Official documentation: https://dev.mysql.com/doc/mysql-errors/8.0/en/server-error-reference.html