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

Published Mar 23 2019 11:03 AM 152 Views
Microsoft
First published on MSDN on Sep 28, 2008

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

%3CLINGO-SUB%20id%3D%22lingo-sub-383481%22%20slang%3D%22en-US%22%3EWorking%20with%20SQL%20Server%202008%20Date%2FTime%20Data%20Types%20using%20v1.2%20JDBC%20driver%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-383481%22%20slang%3D%22en-US%22%3E%0A%20%26lt%3Bmeta%20http-equiv%3D%22Content-Type%22%20content%3D%22text%2Fhtml%3B%20charset%3DUTF-8%22%20%2F%26gt%3B%3CSTRONG%3E%20First%20published%20on%20MSDN%20on%20Sep%2028%2C%202008%20%3C%2FSTRONG%3E%20%3CBR%20%2F%3E%3CP%3ESince%20SQL%20Server%202008%20release%20over%20the%20summer%2C%20people%20have%20been%20asking%2C%20%22So%2C%20does%20the%20existing%20JDBC%20driver%20work%20with%20SQL%20Server%202008%3F%22%20or%20%22When%20will%20there%20be%20a%20SQL%20Server%202008%20JDBC%20driver%3F%22.%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EThere%20are%20many%20things%20to%20consider%20with%20a%20general%20questions%20like%20these.%26nbsp%3B%20What%20features%20of%20SQL%20Server%202008%20are%20you%20truly%20looking%20forward%20to%20leveraging%20and%20can%20the%20features%20be%20used%20through%20existing%20drivers%3F%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EToday%2C%20I%20like%20to%20let%20people%20know%20that%20the%20existing%20v1.2%20JDBC%20driver%20works%20with%20SQL%20Server%202008%20as%20a%20downlevel%20client.%26nbsp%3B%20This%20means%20that%20you%20will%20be%20able%20to%20connect%20to%20a%20SQL%20Server%202008%20instance%20and%26nbsp%3Bexecute%20queries%2Fupdates%26nbsp%3Bon%20majority%20of%20the%20SQL%20Server%202008%20data%20types.%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EThis%20is%20post%2C%20I%20will%20focus%20on%20how%20you%20can%26nbsp%3Bretrieve%20the%20new%20Date%2FTime%2FDatetime2%2FDatetimeOffset%20data%20types%20introduced%20in%20SQL%20Server%202008%20with%20existing%20v1.2%20driver.%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EThe%20first%20question%20you%20may%20ask%2C%20%22So%20what%20is%20the%20metadata%20type%20of%20these%20new%20data%20types%3F%22%20%3CBR%20%2F%3E%20To%20ensure%20full%20data%20fidelity%2C%20SQL%20Server%202008%20has%20chosen%20to%20return%20the%20values%20for%20these%20data%20type%20columns%20as%20%22nvarchar%22.%26nbsp%3B%20This%20means%20that%20all%20older%20SQL%20Server%20clients%20can%20operate%20on%20these%20new%20data%20types%20as%20String%20and%20it%20is%20up%20to%20the%20application%20layer%20to%20parser%20and%20understand%20these%20String%20values.%26nbsp%3B%20The%20v1.2%20driver%20is%20no%20different.%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ETo%20demostrate%20how%20the%20v1.2%20driver%20can%20retrieve%20a%20resultset%20containing%20these%20data%20types%2C%20I%20have%20created%20a%20table%20containing%204%20columns%20(one%20for%20each%20type)%20and%20populated%20the%20table%20with%20some%20sample%20data.%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EHere%20is%20the%20T-SQL%20script%20that%20I%20used%20to%20create%20my%20table%3A%3C%2FP%3E%3CBR%20%2F%3E%3CBLOCKQUOTE%3E%3CBR%20%2F%3E%3CP%3ECREATE%20TABLE%20bar%20%3CBR%20%2F%3E%20(%20%3CBR%20%2F%3E%20%5BDate%5D%20date%2C%20%3CBR%20%2F%3E%20%5BTime%5D%20time%2C%20%3CBR%20%2F%3E%20%5BStartDate%5D%20datetime2%2C%20%3CBR%20%2F%3E%20%5BHireDate%5D%20datetimeoffset%20%3CBR%20%2F%3E%20)%20%3CBR%20%2F%3E%20go%3C%2FP%3E%0A%20%20%3C%2FBLOCKQUOTE%3E%3CBR%20%2F%3E%3CBLOCKQUOTE%3E%3CBR%20%2F%3E%3CP%3Einsert%20bar%20values%20%3CBR%20%2F%3E%20(%20%3CBR%20%2F%3E%20'2008-01-01'%2C%20%3CBR%20%2F%3E%20'13%3A59%3A00.1234567'%2C%20%3CBR%20%2F%3E%20'2008-02-02T20%3A01%3A59.123456789'%2C%20%3CBR%20%2F%3E%20'2008-04-01T10%3A05%3A02%2B08%3A00'%20%3CBR%20%2F%3E%20)%20%3CBR%20%2F%3E%20go%3C%2FP%3E%0A%20%20%3C%2FBLOCKQUOTE%3E%3CBR%20%2F%3E%3CP%3EHere%20is%20the%20code%20snippet%20that%20I%20used%20to%20retrieve%20the%20column%20values%3A%3C%2FP%3E%3CBR%20%2F%3E%3CBLOCKQUOTE%3E%3CBR%20%2F%3E%3CP%3EString%20strCmd%20%3D%20%22select%20*%20from%20bar%22%3B%20%3CBR%20%2F%3E%20ResultSet%20rs%20%3D%20stmt.executeQuery(strCmd)%3B%20%3CBR%20%2F%3E%20if%20(rs.next())%20%3CBR%20%2F%3E%20%7B%20%3CBR%20%2F%3E%20ResultSetMetaData%20rsmd%20%3D%20rs.getMetaData()%3B%20%3CBR%20%2F%3E%20if%20(null%20!%3D%20rsmd)%20%3CBR%20%2F%3E%20%7B%20%3CBR%20%2F%3E%20int%20count%20%3D%20rsmd.getColumnCount()%3B%20%3CBR%20%2F%3E%20for%20(int%20i%20%3D%201%3B%20i%20%26lt%3B%3D%20count%3B%20i%2B%2B)%20%3CBR%20%2F%3E%20%7B%20%3CBR%20%2F%3E%20System.out.println(%22%22)%3B%20%3CBR%20%2F%3E%20String%20nameType%20%3D%20rsmd.getColumnTypeName(i)%3B%20%3CBR%20%2F%3E%20String%20name%20%3D%20rsmd.getColumnName(i)%3B%20%3CBR%20%2F%3E%20System.out.println(%22Column%20%22%20%2B%20name%20%2B%20%22%20is%20data%20type%3A%20%22%20%2B%20nameType)%3B%20%3CBR%20%2F%3E%20String%20str%20%3D%20rs.getString(i)%3B%20%3CBR%20%2F%3E%20System.out.println(%22Column%20%22%20%2B%20name%20%2B%20%22%3A%20%22%20%2B%20str)%3B%20%3CBR%20%2F%3E%20%7D%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EDate%20dt%20%3D%20rs.getDate(1)%3B%20%3CBR%20%2F%3E%20System.out.println(%22Column%201%20has%20value%3A%20%22%20%2B%20dt.toString())%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ETime%20t%20%3D%20rs.getTime(2)%3B%20%3CBR%20%2F%3E%20System.out.println(%22Column%202%20has%20value%3A%20%22%20%2B%20t.toString())%3B%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ETimestamp%20ts%20%3D%20rs.getTimestamp(3)%3B%20%3CBR%20%2F%3E%20System.out.println(%22Column%203%20has%20value%3A%20%22%20%2B%20ts.toString())%3B%20%3CBR%20%2F%3E%20%7D%20%3CBR%20%2F%3E%20%7D%20%3CBR%20%2F%3E%20rs.close()%3B%20%3CBR%20%2F%3E%20stmt.close()%3B%3C%2FP%3E%0A%20%20%3C%2FBLOCKQUOTE%3E%3CBR%20%2F%3E%3CP%3EThe%20output%20looks%20like%3A%3C%2FP%3E%3CBR%20%2F%3E%3CBLOCKQUOTE%3E%3CBR%20%2F%3E%3CP%3EColumn%20Date%20is%20data%20type%3A%20nvarchar%20%3CBR%20%2F%3E%20Column%20Date%3A%202008-01-01%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EColumn%20Time%20is%20data%20type%3A%20nvarchar%20%3CBR%20%2F%3E%20Column%20Time%3A%2013%3A59%3A00.1234567%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EColumn%20StartDate%20is%20data%20type%3A%20nvarchar%20%3CBR%20%2F%3E%20Column%20StartDate%3A%202008-02-02%2020%3A01%3A59.1234568%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EColumn%20HireDate%20is%20data%20type%3A%20nvarchar%20%3CBR%20%2F%3E%20Column%20HireDate%3A%202008-04-01%2010%3A05%3A02.0000000%20%2B08%3A00%20%3CBR%20%2F%3E%20Column%201%20has%20value%3A%202008-01-01%20%3CBR%20%2F%3E%20Column%202%20has%20value%3A%2013%3A59%3A00%20%3CBR%20%2F%3E%20Column%203%20has%20value%3A%202008-02-02%2020%3A01%3A59.1234568%3C%2FP%3E%0A%20%20%3C%2FBLOCKQUOTE%3E%3CBR%20%2F%3E%3CP%3ENote%3A%20For%20the%20DatetimeOffset%20column%2C%20the%20v1.2%20JDBC%20driver%20is%20unable%20to%20automatically%20create%20a%20Timestamp%20object%20from%20the%20String%2C%20through%20rs.getTimestamp()%20due%20to%20the%20Timestamp%20class%20not%20supporting%20time%20zone%20information.%26nbsp%3B%20To%20leverage%20the%20Calendar%20or%20GregorianCalendar%20classes%20which%20do%20support%20time%20zone%2C%20the%20application%20will%20need%20to%20parse%20the%20string%20in%20order%20to%20create%20the%20Calendar%20object.%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EJimmy%20Wu%20%3CBR%20%2F%3E%20SQL%20Server%20JDBC%20Team%3C%2FP%3E%0A%20%0A%3C%2FLINGO-BODY%3E%3CLINGO-TEASER%20id%3D%22lingo-teaser-383481%22%20slang%3D%22en-US%22%3EFirst%20published%20on%20MSDN%20on%20Sep%2028%2C%202008%20Since%20SQL%20Server%202008%20release%20over%20the%20summer%2C%20people%20have%20been%20asking%2C%20%22So%2C%20does%20the%20existing%20JDBC%20driver%20work%20with%20SQL%20Server%202008%3F%22%20or%20%22When%20will%20there%20be%20a%20SQL%20Server%202008%20JDBC%20driver%3F%22.%3C%2FLINGO-TEASER%3E%3CLINGO-LABS%20id%3D%22lingo-labs-383481%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3ESQLServerDrivers%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Version history
Last update:
‎Mar 23 2019 11:03 AM
Updated by: