Conversion of a datetime2 data type to a datetime data type resulted in an out-of-range value

[grahama]

Table I am trying to import: canvas.assignments

I am running into a datetime2 into datetime out of range exception from pyodbc. The MS SQL plugin code I have written is written similarly to their provided MySQL plugin. Based on the data from the file it does not appear to be a min/max date-related issue.

sqlalchemy.exc.DataError: (pyodbc.DataError) ('22007', '[22007] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]The conversion of a datetime2 data type to a datetime data type resulted in an out-of-range value. (242) (SQLParamData); [22007] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]The statement has been terminated. (3621)')

[grahama · Accepted solution]

I found the issue. The DAP client only uses a method for clamping date/times to min/max values for non-nullable columns. Some records in a column allow null with a date/time value less than what MS SQL server allows for the datetime property. I was looking into using datetime2 in the metadata file but the DAP client's conversion_common_json.py file only accounts for TIMESTAMP and DATETIME for SQL Alchemy. I wanted to avoid re-writing code that was already provided so instead I created another method that gets called when the records are converted to check all DateTime objects for MIN/MAX values. 

    def _check_date_min_max(self, record: Any) -> Any:
        """
        This method is used to validate that the date object is within the bounds MS SQL server allows.
        The DAP client does not use DATETIME2 so the easiest workaround is to clamp any out-of-bound dates.
        Encountered error: The conversion of a datetime2 data type to a datetime data type resulted in an out-of-range value.
        """
        if isinstance(record, datetime.datetime):
            if record < timestamp.DATETIME_MIN:
                record = timestamp.DATETIME_MIN
            elif record > timestamp.DATETIME_MAX:
                record = timestamp.DATETIME_MAX
        return record