DateTimeOffset type to TIMESTAMP_TZ issue with large dates

[skornsek]

I noticed an issue when inserting a large date like '9999-12-30T23:00:00.0000000+00:00'. It is stored in Snowflake as '1816-03-29 04:56:08.066 +0000'

When logging the parameter value in DbCommand is '9999-12-30T23:00:00.0000000+00:00'

Also when testing on Snowflake the date seems ok.

Column settings

b.Property<DateTimeOffset?>("EndDt")
    .HasColumnType("TIMESTAMP_TZ(9)")
    .HasColumnName("END_DT");

Dates around 2300-12-31 work. Dates bigger than 2500-12-31 also get corrupted.

Log output with parameters (:p8) also seems ok. Could this be an issue with Snowflake.Data?

INSERT INTO "CLASSIFICATION_LINES" ("ADDITIONAL_COL_VAL", "CLASSIFICATION_LINE_PARENT_ID", "CLASSIFICATION_TYPE_ID", "CODE", "CREATED", "CREATED_BY", "DESC", "EFF_DT", "END_DT", "LAST_MODIFIED", "LAST_MODIFIED_BY", "NAME", "SORT_NUM")
SELECT :p0, :p1, :p2, :p3, :p4, :p5, :p6, :p7, :p8, :p9, :p10, :p11, :p12;
Microsoft.EntityFrameworkCore.Database.Command: Information: Executed DbCommand (374ms) [Parameters=[p0='{"values":{}}', p1=NULL (DbType = Int64), p2='4', p3='XXXXX' (Nullable = false), p4='2024-10-08T16:14:12.5910721+00:00', p5='XXXX', p6='XXXXXX', p7='1899-12-31T23:00:00.0000000+00:00', p8='9999-12-30T23:00:00.0000000+00:00', p9='2024-10-08T16:14:12.5910721+00:00' (Nullable = true), p10='Codify', p11='XXXXX' (Nullable = false), p12='0'], CommandType='Text', CommandTimeout='0']

[skornsek]

I tried directly with Snowflake.Data version 4.1.0 and when executing the same insert using this parameter the TIMESTAMP_TZ gets corrupted.

command.Parameters.Add(new SnowflakeDbParameter("p8", SFDataType.TIMESTAMP_TZ) { Value = DateTimeOffset.Parse("9999-12-30T23:00:00.0000000+00:00") });

I opened an issue about that on the snowflake-connector-net repo: snowflakedb/snowflake-connector-net#1036

[Sielnix]

Thank you @skornsek for your report. With the next release of EFCore.Snowflake, latest version of Snowflake.Data will be used.