Working with SQL Server 2008 Date/Time Data Types using v1.2 JDBC driver
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-01Column Time is data type: nvarchar
Column Time: 13:59:00.1234567Column StartDate is data type: nvarchar
Column StartDate: 2008-02-02 20:01:59.1234568Column 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
Comments
Anonymous
October 03, 2008
In the previous post "Working with SQL Server 2008 Date/Time Data Types using v1.2 JDBC driver" . I talkedAnonymous
October 08, 2008
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?Anonymous
January 08, 2009
The comment has been removedAnonymous
June 14, 2009
The comment has been removedAnonymous
June 17, 2009
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]Anonymous
October 18, 2009
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?Anonymous
October 19, 2009
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]Anonymous
October 22, 2009
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. SteveAnonymous
January 25, 2010
how to use this Driver????? and what we write instead of url? and what is url name?Anonymous
January 25, 2010
what we write instead of url? and what is url name?Anonymous
March 25, 2010
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 AminaAnonymous
August 25, 2010
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!