Mapping DateTime-Timestamp on Java17

[diegofalcinelli]

Hi,

i have a problem on table SQLServer 2017
in a Column

LastUpdate	datetime

MAPPING

mapDateTime("LastUpdate", (TitleDetail t) -> {
            if (t.getLastUpdate() == null) {
                return Timestamp.valueOf(t.getLastUpdate());
            }
            return Timestamp.valueOf(LocalDateTime.now(Clock.systemUTC()));
        });



<dependency>
      <groupId>com.microsoft.sqlserver</groupId>
      <artifactId>mssql-jdbc</artifactId>
      <version>11.2.3.jre17</version>
  </dependency>

RUN on java 17.0.9

Error
java.lang.RuntimeException: com.microsoft.sqlserver.jdbc.SQLServerException: Conversion failed when converting date and/or time from character string. at de.bytefish.jsqlserverbulkinsert.SqlServerBulkInsert.saveAll(SqlServerBulkInsert.java:53) at de.bytefish.jsqlserverbulkinsert.SqlServerBulkInsert.saveAll(SqlServerBulkInsert.java:31) at de.bytefish.jsqlserverbulkinsert.SqlServerBulkInsert.saveAll(SqlServerBulkInsert.java:62)

Into Java 8.0.371 &

 <dependency>
            <groupId>com.microsoft.sqlserver</groupId>
            <artifactId>mssql-jdbc</artifactId>
            <version>8.4.1.jre8</version>
        </dependency>

it WORK!!!! Why??

Error in all version jsqlserverbulkinsert, but used :

<dependency>
            <groupId>de.bytefish</groupId>
            <artifactId>jsqlserverbulkinsert</artifactId>
            <version>4.0.1</version>
            <type>jar</type>
        </dependency>

[bytefish]

I will try it, but I cannot guarantee when. I‘ll leave this ticket open, so I don’t forget about it.

[bytefish]

Could you share the SQL Type of the “LastUpdated“ column? Is it a DATETIME2(7)?

[diegofalcinelli]

[LastUpdate] datetime DEFAULT getutcdate() NULL,

[diegofalcinelli]

news?

[bytefish]

@diegofalcinelli Sorry it took so long. This is a cool bug!

Actually the problem must be something like this: A LocalDateTime.now() returns a value in nanosecond precision, but a DATETIME column in SQL Server doesn't support this level of precision. And instead of truncating to something supported at JDBC driver level, it just inserts it with the given precision... and throws the error we have seen.

If you cannot switch to a DATETIME2(7) column type you can truncate the LocalDateTime to Millisecond Precision like this:

public class Issue21EntityMapping extends AbstractMapping<Issue21Entity> {

    public Issue21EntityMapping() {
        super("dbo", "UnitTest");

        mapLocalDateTime("LastUpdated", x -> x.getLastUpdate().truncatedTo(ChronoUnit.MILLIS));
    }  
}

Alternatively I would need to introduce the LocalDateTime truncation in the JSqlServerBulkInsert code, but I am not that confident about the changes at the moment. It would require checking the Column Type at time of mapping and I really don't want to turn this project into an OR-Mapper.