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://learn.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.

Quinsy

In the statement Object o = resultSet.getObject("date_from"); , what is the type of table-column date_from? – Mabelmabelle

From exception, "date_from" cannot be converted to LocalDateTime. Can you print out what you did you get from Object o = resultSet.getObject("date_from");? You did not tell us what date_from is as we need to have a look of the format. – Scott

Database type of date_from is 'datetime'. Tried with these classes, but still get an exception: LocalDateTime, LocalDate, LocalTime, OffsetDateTime, OffsetTime – Quinsy

Check this answer and this answer to learn how to use java.time API with JDBC. – Razzledazzle

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".

Calica

Thanks for reporting that, I had put it on my mental to-do list and promptly forgot :) – Javier

@MarkRotteveel - I submitted a pull request to add LocalDateTime support via getObject. It has prompted a discussion about additional "convenience" methods that might be added, presumably as extensions to the JDBC API. I'd be grateful if you could contribute to that discussion given your extensive knowledge of JDBC from both the "spec" and "real world" perspectives. Cheers. – Calica

I left a few comments on that pull request. – Javier

Thanks for (updated) answer and workaround :) Thats better solution than using formatter. – Quinsy

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()

Starr

A JDBC 4.2 compliant driver should be able to return it using ResultSet.getObject("date_from", LocalDateTime.class) assuming the underlying field is a timestamp (datetime) – Javier

I thought that it is fully compliant with JDBC 4.2 - it looks like it doesn't. So everytime to get java.time.* object from ResultSet I will have to check for null, and then convert using your suggestion... – Quinsy

(cc: @Quinsy ) Watch out for Daylight Saving Time issues. See my answer for details. – Calica

Answer 3

It looks like the mssql-jdbc driver doesn't fully implement the java.time support specified in JDBC 4.2. It doesn't support it for ResultSet.getObject, but it does support it for PreparedStatement.setObject.

The workaround, as suggested by the answer of Loc Le, is to retrieve as Timestamp and convert that to a LocalDateTime.

Javier

Answer 4

If you are stuck and want to try the "old way" of doing it:

Here is a simples example of equivalent conversions. Test yourself.

@Test
public void testingConversionTimestampAndLocalDateTime(){
    Timestamp initialTimestamp = new Timestamp(System.currentTimeMillis());

    LocalDateTime localDateTime = LocalDateTime.ofInstant(initialTimestamp.toInstant(), ZoneId.of("America/Sao_Paulo"));

    ZoneOffset zoneOffset = ZoneId.of("America/Sao_Paulo").getRules().getOffset(localDateTime);
    Timestamp timestamp = Timestamp.from(localDateTime.toInstant(zoneOffset));

    Timestamp timestamp1 = Timestamp.valueOf(localDateTime);

    assertEquals(initialTimestamp, timestamp);
    assertEquals(initialTimestamp, timestamp1);
}

Alfano