Return extra ".0" when reading "smalldatetime" column with PreparedStatement

Question

I have a table in SQL Server with a column type smalldatetime and I am using PreparedStatement to read/write data in my tables. E.g My table is like:

|         date           |
+------------------------+
|   2019-11-06 09:48:00  +
|   2019-11-05 07:04:00  +
|         ...            +
+------------------------+

and I am reading the column date with:

String date = rs.getString("date");

And I get an extra .0 at the end of the date:

2019-11-06 09:48:00.0
2019-11-05 07:04:00.0

Why is this happening?

why not do a `getDate` then? Then you can format it to however you want. — Scary Wombat, Nov 06 '19 at 08:05
I tried it and `getDate` returns only the date (e.g. 2019-11-06) and not the time... — yaylitzis, Nov 06 '19 at 08:07
@yaylitzis Try using `ResultSet#getTimestamp()` then, and ideally use a `LocalDateTime` on the Java side. — Tim Biegeleisen, Nov 06 '19 at 08:09
I tried it and `getTimestamp` also returns the `.0` in the end.. I tested with `System.out.println(rs.getTimestamp("date"));` — yaylitzis, Nov 06 '19 at 08:11
see https://stackoverflow.com/questions/24736427/how-to-get-date-from-a-resultset/24737131 — Scary Wombat, Nov 06 '19 at 08:13
This looks purely like a formatting thing; the value hasn't been changed its just not being displayed as the OP expects. — Thom A, Nov 06 '19 at 08:16
ok thanks @Larnu. As you said, it seems to be just a formatting issue.. — yaylitzis, Nov 06 '19 at 08:18

Andreas · Accepted Answer · 2019-11-06T08:41:50.090

The underlying SQL Server JDBC driver converts the returned bytes into a GregorianCalendar first and then calls toString on a newly created Timestamp based on this GregorianCalendar. This causes the trailing .0.

case CHARACTER: {
  ...many other conversions...
  switch (ssType) {
    case DATETIME: // and SMALLDATETIME
    {
      return (new java.sql.Timestamp(cal.getTimeInMillis())).toString();
    }

mssql-jdbc Conversion Code mssql-jdbc SMALLDATETIME handling

So what the mssql-jdbc code actually executes is:

System.out.println(new java.sql.Timestamp(100).toString());

> 1970-01-01 00:00:00.1

JavaDoc Timestamp#toString

I was not aware that the mssql-jdbc driver is MIT licensed and on github :-) — Andreas, Nov 06 '19 at 08:45

Return extra ".0" when reading "smalldatetime" column with PreparedStatement

1 Answers1