Struggling with error: Inserting value to batch for column type DATETIME2 failed.

Question

We've been running queries on our serverless sql pool using OPENROWSET to read parquet files in an azure gen2 datalake. Yesterday, the queries started throwing this error: Inserting value to batch for column type DATETIME2 failed. No explicit changes had been made in our processes. In checking the data in the parquet files, there were date values coming from the source that looked like this: '0001-01-01 00:00:00'. Previous to yesterday, those values were changed to '1754-08-30 22:43:41.128654848' by the copy activity that wrote the parquet files. Beginning yesterday, those dates were now written as '1754-08-28 22:43:41.128654848'; a difference of two days. The OPENROWSET query will read records with values of 1754-08-30 but not 1754-08-28. This change in date value isn't something we've created explicitly but is happening implicitly in the copy activity. The only system change I can find is that our integration runtime we are using to read the data from the source had an automatic update applied right before this issue began. Any thoughts on 1. Why the date values are offset by two days compared to what they used to be? and 2. Why the OPENROWSET query can't read the records anymore? 1754 should be within the range of a DATETIME2(7) type.

Answer 1

The error you're encountering, "Inserting value to batch for column type DATETIME2 failed," coupled with the change in behavior regarding the DATETIME2 field suggests two potential root causes:

1. The fact that dates are being offset by two days (1754-08-30 versus 1754-08-28) suggests that something in your pipeline, likely related to your integration runtime, is adjusting dates due to a timezone shift or a locale-specific setting. The automatic update that was applied to your integration runtime could have changed how the datetime values are handled, especially during the copy activity. You might want to check if the update involved changes in timezone settings or handling of specific date formats.
2. The DATETIME2 type has a minimum valid date of '0001-01-01', but if your source data contains invalid or borderline dates (e.g., '0001-01-01 00:00:00'), the copy activity or the SQL query might encounter issues while converting or storing them. If the process previously worked with dates like '1754-08-30' but is now failing for '1754-08-28', it's possible that there is stricter validation or handling in the recent update. While both dates should be within the valid range of DATETIME2(7), this discrepancy may reflect some internal check or conversion issue.

Suggestions for Resolution:

1. Verify if any timezone or locale-related settings were modified as part of the update. If possible, revert or explicitly set the timezone handling in the integration runtime to the previous settings.
2. Review your copy activity's source and sink settings, specifically for how dates are transformed or copied. Ensure that the correct date mappings or format conversions are applied consistently.
3. Consider filtering out or transforming these problematic dates at the time of reading or insertion. You might explicitly convert or cast these dates to handle them in a way that avoids the issue, for example:

      
      SELECT CASE WHEN [DateColumn] < '1754-08-30' THEN '1754-08-30' ELSE [DateColumn] END AS [FixedDateColumn]
      
      FROM OPENROWSET(...)
      
   

Given that this issue started after the integration runtime update, it could be worthwhile to review recent updates or logs from Azure, specifically focusing on any known issues or bug fixes related to datetime handling in Synapse or ADF.