LocalDateTime and SQL Server JDBC 4.2 driver

Question

I'm trying to use new java.time classes with most recent version of Sql Server JDBC driver. As I read it should just work with methods: PreparedStatement.setObject() and ResultSet.getObject().

So I created sample code, and can't get it work with ResultSets. I don't know what I'm doing wrong here.

Connection connection = DriverManager.getConnection(connectionString);
PreparedStatement preparedStatement = connection.prepareStatement("SELECT * FROM myTable WHERE ? BETWEEN date_from AND date_to");
preparedStatement.setObject(1, LocalDateTime.now());   // That works

ResultSet resultSet = preparedStatement.executeQuery();
if (resultSet.next()) {
    Object o = resultSet.getObject("date_from"); 
    o.getClass() returns java.sql.Timestamp

    LocalDateTime dateTime = resultSet.getObject("date_from", LocalDateTime.class);
}

This throws an exception:

com.microsoft.sqlserver.jdbc.SQLServerException: The conversion to class java.time.LocalDateTime is unsupported.

Driver version: mssql-jdbc-6.5.4.jre8-preview.jar

SQL Server version: 2016

---

https://docs.microsoft.com/en-us/sql/connect/jdbc/jdbc-4-2-compliance-for-the-jdbc-driver?view=sql-server-2017

How to interpret this sentence in table at bottom:

New Java classes in Java 8: LocalDate/LocalTime/LocalDateTime, OffsetTime/OffsetDateTime

New JDBC types: TIME_WITH_TIMEZONE, TIMESTAMP_WITH_TIMEZONE, REF_CURSOR

REF_CURSOR is not supported in SQL Server. Driver throws a SQLFeatureNotSupportedException exception if this type is used. The driver supports all other new Java and JDBC type mappings as specified in the JDBC 4.2 specification.

Answer 1

I don't know what I'm doing wrong here.

You're not doing anything wrong. You have encountered a deficiency in Microsoft's JDBC driver for SQL Server prior to version 7.1.0, discussed here.

If you are using mssql-jdbc version 7.1.0 or later then you can use getObject(x, LocalDateTime.class) as expected.

For mssql-jdbc versions prior to 7.1.0, as others have suggested, you'll need to retrieve a Timestamp and convert it to a LocalDateTime. However, be aware that the simplistic solution ...

LocalDateTime dateTime = resultSet.getTimestamp("date_from").toLocalDateTime()

... will corrupt certain date/time values if the default time zone for the JVM observes Daylight Saving Time, a.k.a. "Summer Time". For example,

// time zone with Daylight Time
TimeZone.setDefault(TimeZone.getTimeZone("America/Edmonton"));

// test environment
Statement st = conn.createStatement();
st.execute("CREATE TABLE #tmp (id INT PRIMARY KEY, dt2 DATETIME2)");
st.execute("INSERT INTO #tmp (id, dt2) VALUES (1, '2018-03-11 02:00:00')");
ResultSet rs = st.executeQuery("SELECT dt2 FROM #tmp WHERE id=1");
rs.next();

// test code
LocalDateTime x = rs.getTimestamp("dt2").toLocalDateTime();  // bad

System.out.println(x.toString());

will print "2018-03-11T03:00". Note that the time is "03:00", not "02:00".

Instead, you'll need to retrieve the Timestamp as UTC and then convert it into a LocalDateTime for UTC, thus removing the time zone component

// time zone with Daylight Time
TimeZone.setDefault(TimeZone.getTimeZone("America/Edmonton"));

// test environment
Statement st = conn.createStatement();
st.execute("CREATE TABLE #tmp (id INT PRIMARY KEY, dt2 DATETIME2)");
st.execute("INSERT INTO #tmp (id, dt2) VALUES (1, '2018-03-11 02:00:00')");
ResultSet rs = st.executeQuery("SELECT dt2 FROM #tmp WHERE id=1");
rs.next();

// test code
Timestamp ts = getTimestamp("dt2", Calendar.getInstance(TimeZone.getTimeZone("UTC")));
LocalDateTime x = LocalDateTime.ofInstant(ts.toInstant(), ZoneId.of("UTC"));  // good

System.out.println(x.toString());

which prints "2018-03-11T02:00".

Answer 2

This is because the Microsoft SQL Server JDBC driver implementation of resultSet.getObject(...) cannot auto convert from java.sql.Timestamp to LocalDateTime.

As a workaround you can get the value as java.sql.Timestamp and then convert java.sql.Timestamp to LocalDateTime by using: java.sql.Timestamp.toLocalDateTime()

LocalDateTime dateTime = resultSet.getTimestamp("date_from").toLocalDateTime()