Working with SQL Server 2008 Date/Time Data Types using v1.2 JDBC driver

Working with SQL Server 2008 Date/Time Data Types using v1.2 JDBC driver

  • Comments 12

Since SQL Server 2008 release over the summer, people have been asking, "So, does the existing JDBC driver work with SQL Server 2008?" or "When will there be a SQL Server 2008 JDBC driver?".

There are many things to consider with a general questions like these.  What features of SQL Server 2008 are you truly looking forward to leveraging and can the features be used through existing drivers?

Today, I like to let people know that the existing v1.2 JDBC driver works with SQL Server 2008 as a downlevel client.  This means that you will be able to connect to a SQL Server 2008 instance and execute queries/updates on majority of the SQL Server 2008 data types.

This is post, I will focus on how you can retrieve the new Date/Time/Datetime2/DatetimeOffset data types introduced in SQL Server 2008 with existing v1.2 driver.

The first question you may ask, "So what is the metadata type of these new data types?"
To ensure full data fidelity, SQL Server 2008 has chosen to return the values for these data type columns as "nvarchar".  This means that all older SQL Server clients can operate on these new data types as String and it is up to the application layer to parser and understand these String values.  The v1.2 driver is no different.

To demostrate how the v1.2 driver can retrieve a resultset containing these data types, I have created a table containing 4 columns (one for each type) and populated the table with some sample data.

Here is the T-SQL script that I used to create my table:

CREATE TABLE bar
(
    [Date] date,
    [Time] time,
    [StartDate] datetime2,
    [HireDate] datetimeoffset
)
go

insert bar values
(
  '2008-01-01',
  '13:59:00.1234567',
  '2008-02-02T20:01:59.123456789',
  '2008-04-01T10:05:02+08:00'
)
go

Here is the code snippet that I used to retrieve the column values:

 String strCmd = "select * from bar";
 ResultSet rs = stmt.executeQuery(strCmd);
 if (rs.next())
 {
  ResultSetMetaData rsmd = rs.getMetaData();
  if (null != rsmd)
  {
   int count = rsmd.getColumnCount();
   for (int i = 1; i <= count; i++)
   {
    System.out.println("");
    String nameType = rsmd.getColumnTypeName(i);
    String name = rsmd.getColumnName(i);
    System.out.println("Column " + name + " is data type: " + nameType);
    String str = rs.getString(i);
    System.out.println("Column " + name + ": " + str);
   }

   Date dt = rs.getDate(1);
   System.out.println("Column 1 has value: " + dt.toString());

   Time t = rs.getTime(2);
   System.out.println("Column 2 has value: " + t.toString());

   Timestamp ts = rs.getTimestamp(3);
   System.out.println("Column 3 has value: " + ts.toString());
  }
 }
 rs.close();
 stmt.close();   

The output looks like:

Column Date is data type: nvarchar
Column Date: 2008-01-01

Column Time is data type: nvarchar
Column Time: 13:59:00.1234567

Column StartDate is data type: nvarchar
Column StartDate: 2008-02-02 20:01:59.1234568

Column HireDate is data type: nvarchar
Column HireDate: 2008-04-01 10:05:02.0000000 +08:00
Column 1 has value: 2008-01-01
Column 2 has value: 13:59:00
Column 3 has value: 2008-02-02 20:01:59.1234568

Note: For the DatetimeOffset column, the v1.2 JDBC driver is unable to automatically create a Timestamp object from the String, through rs.getTimestamp() due to the Timestamp class not supporting time zone information.  To leverage the Calendar or GregorianCalendar classes which do support time zone, the application will need to parse the string in order to create the Calendar object.

Jimmy Wu
SQL Server JDBC Team

Leave a Comment
  • Please add 2 and 5 and type the answer here:
  • Post
  • In the previous post "Working with SQL Server 2008 Date/Time Data Types using v1.2 JDBC driver" . I talked

  • Hi, thanks for the article, and I look forward to working with SqlServer 2008!

    So, when will there be a dedicated SQL Server 2008 JDBC driver? I guess this is still a while away?

  • @Jimmy

    Intesreting post but you are not answering the only relevant question: when a SQL Server 2008 JDBC driver will be available?

    At this time I understand that there is no.

  • hi jimmy. nice post thanks a lot.

    but my only question is, can i use the 2.0 driver for the new sql-server 2008 types? i mean can i send an insert-query like this:

    ----------------------------

    insert bar values

    (

     '1291-08-01', --note before 1753

     '13:59:00.1234567', --note before 1753

     '1291-08-01T20:01:59.123456789', --note before 1753

     '1291-04-01T10:05:02+08:00' --note before 1753

    )

    go

    ----------------------------

    thanks for you answer.

    cheers, gilles

  • Hello Gilles,

    The new 2.0 driver, like the 1.2 driver, was not written to specifically support SQL Server 2008 data types. However, that does not mean you are completely locked out. If you follow the guidelines of this blog post you should be able to access/update Date/Time/Datetime2/DatetimeOffset data types introduced in SQL Server 2008. Essentially, for SQL Server 2008 data type access, our JDBC 1.2 and 2.0 drivers work the same.

    --Tres London [SQL Server]

  • Is there any way to work out if a column is a date/time/datetime2 column if you don't know anything about the query sql?

  • Hey Steve,

    I'm not sure what your scenario is. However, this should be able to retrieve the SQL Server specific column type:

    http://java.sun.com/javase/6/docs/api/java/sql/DatabaseMetaData.html#getColumns(java.lang.String,%20java.lang.String,%20java.lang.String,%20java.lang.String)

    Function: getColumns

    attribute: type_name

    --Tres London [SQL Server]

  • Thanks,

    This is great if you know what table the data is coming from. I'm working on a tool that allows users to enter an SQL query to run against the database. Without writing my own SQL parser I don't know what tables they are using in their query. If they query a date/time/datetime2 column, the ResultSetMetaData tells me that it's an nvarchar column, and I can't see any way of determining that a call to rs.getDate() will work.

    Steve

  • how to use this Driver?????

    and what we write instead of url?

    and what is url name?

  • what we write instead of url?

    and what is url name?

  • Hi,

    We are working on our upcoming release JDBC 3.0 which provides support for Date/Time Data types in SQL Server. You can find more information on our upcoming release at:

    http://blogs.msdn.com/jdbcteam/archive/2010/03/02/microsoft-sql-server-jdbc-3-0-ctp-release-announcement.aspx

    Thanks

    Amina

  • Hi, is the 1.2 driver indeed supported with SQL Server 2008?  The 1.2 download page at www.microsoft.com/.../details.aspx only lists versions 2000 and 2005.  Only the 2.0 and 3.0 driver download pages list 2008 in the System Requirements page.  Thanks!

Page 1 of 1 (12 items)