The way that datetime is handled by mssql-jdbc is flawed. datetime types are time-zoneless, however, the driver maps them to the JVM time zone when deserializing the datetime sent from SQLServer.

The test program below shows what happens when reading the output of select cast('2017-03-26T02:00:00' as datetime2).

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.time.ZoneId;
import java.time.format.DateTimeFormatter;

public class Main1 {
    public static void main(String[] args) throws SQLException {
        String url = "jdbc:sqlserver://localhost:1433";
        String user = "sa";
        String password = "Test1234!";
        System.out.println("-Duser.timezone=" + ZoneId.systemDefault());
        try (
                Connection c = DriverManager.getConnection(url, user, password);
        ) {
            String expected = "2017-03-26T02:00:00";
            try (Statement s = c.createStatement()) {
                s.execute(String.format("select cast('%s' as datetime2)", expected));
                try (ResultSet r = s.getResultSet()) {
                    r.next();
                    LocalDateTime localDateTime = r.getTimestamp(1).toLocalDateTime();
                    String actual = localDateTime.format(DateTimeFormatter.ISO_LOCAL_DATE_TIME);
                    System.out.format("Main1: %s == %s %s%n", expected, actual, (expected.equals(actual) ? "OK" : "FAILED!"));
                }
            }
        }
    }
}

When running the program in UTC all is working nicely.

-Duser.timezone=UTC
Main1: 2017-03-26T02:00:00 == 2017-03-26T02:00:00 OK

However, in Europe/Stockholm (or CET) things fails.

-Duser.timezone=Europe/Stockholm
Main1: 2017-03-26T02:00:00 == 2017-03-26T03:00:00 FAILED!

Obviously this comes down to that 2017-03-26T02:00:00 in Europe/Stockholm falls just on the start of the DST gap. The driver internally converts the local date time to a zoned date time using java.util.Calendar and java.sql.Timestamp. Those classes can't work with truly local date times since they automatically move date times inside gaps to the next non-gap datetime which is 2017-03-26T03:00:00.

The problem is that local date times are linear:

• The second after 2017-03-26T01:59:59 is 2017-03-26T02:00:00

While zoned date times are non-linear (because of how they are mapped on to UTC):

• In Europe/Stockholm the second after 2017-03-26T01:59:59 is 2017-03-26T03:00:00.

Therefore we can't use java.util.Calendar and java.sql.Timestamp to represent local date times correctly unless we work in UTC. For other time zones the conversion between local data times -> zoned date times -> local date times is destructive.

A workaround in application code is to replace the call r.getTimestamp(1).toLocalDateTime() above with the more complex:

 LocalDateTime localDateTime = r.getTimestamp(1, Calendar.getInstance(TimeZone.getTimeZone("UTC"))).toInstant().atZone(ZoneOffset.UTC).toLocalDateTime();

Note that database IDE:s with database integrations such as IntelliJ or DbVisualizer aren't aware of this. They simply call ResultSet.getObject(). For example, the result of the below query

select
  t.a,
  t.b,
  datediff(minute, a, b) diff_minutes
from (
  select
    cast('2017-03-26T02:00:00' as datetime2) a, cast('2017-03-26T03:00:00' as datetime2) b
) t

is displayed like this in IntelliJ (in fact, this was how I spotted the problem in the first place).

1. Note that jtds works. This is because their getObject() returns a String and not a Timestamp.
2. Note that Oracles SqlDeveloper also works (for oracle). They might have implemented the workaround.

I understand that the problem here is bigger then the mssql-jdbc and that it comes down to JDBC and its use of the old legacy classes Calendar and Timestamp. However, maybe mssql-jdbc can work with date times (and such types) in an internal format so that e.g. getTimestamp() keeps its old behavior and that getObject() returns a LocalDateTime?