Handling negative/invalid Datetimes gracefully

Hello community, how is everyone?

I am currently facing an issue which happens infrequently but can be quite troublesome and I am kind of at my wits end :/

Currently I am using Dapper for a project which performs data extractions from many different tables of different databases in an overall generic way (think select * from X in an Oracle database, then on a SQL Server, and so on), and all that data goes to a centralized place (the company datalake).

This works well overall, but since these tables are populated by application users, these often make mistakes when inserting data which they sometimes refuse to correct (and since they own their data, we cannot forcibly change it). One example of this is basically a negative datetime.

Oracle allows usage of negative datetimes with no issue, but when converting to DateTime in .NET we are restricted to positive datetimes, so I receive Unhandled exception. System.ArgumentOutOfRangeException: Year, Month, and Day parameters describe an un-representable DateTime.

A simple way to see this in action is to basically do:

CREATE TABLE test(
    test_id NUMBER GENERATED BY DEFAULT AS IDENTITY,
    dt_xpto DATE,
    PRIMARY KEY(test_id)
);
INSERT INTO test (dt_xpto) VALUES (DATE '-2021-12-31');

(Image from a dbeaver screenshot showing that this can be selected with no issues)

I then have this code:

        using (var connection = new OracleConnection("Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=ORCLCDB.localdomain)));User Id = xpto; Password = 1234;"))
        {
            var xpto = connection.Query<dynamic>("SELECT * FROM teste");

            Console.WriteLine($"records:  {xpto.Count()}");
        }

Which works fine and does what I need in most cenarios, but for these pesky negative DateTimes I simply end up with the unhandled exception mentioned. This is specially bad when I have a thousand records and a single bad one prevents me from running the entire process.

Is there any sort of way (through reflection, extension methods, built-ins, or straight up magic) which can enable me to read these records?

I would ideally love something like a try/except where it would use the normal DateTime conversion, but if it hits the unhandled exception, then maybe I'd try returning it as a string or even better, casting the datetime to a positive year.

If this is too off-topic as an issue or if I am not following some kind of proper issue template, please let me know and I will gladly rewrite the issue.

Kind regards,

No project files or tests are named. Start by reproducing the provided Oracle query through Dapper and tracing where the negative DATE becomes a .NET DateTime; completion would require an agreed fallback behavior and regression coverage, neither of which is specified here.