Using Time and Date Data Types - Part 1 (It's about time)
While the JDBC driver product team is busy putting the final touches on the Microsoft SQL Server JDBC Driver 3.0 release, I’d like to present some of the new functionality enabled for time and date data types introduced in SQL Server 2008. As I started going through the features (time type, date type, datetimeoffset, historical (pre-Gregorian) dates, 100-nanosecond precision, ...) I realized there was just too much material for one post. So this post starts a series on time and date data type functionality: each post covering a different aspect of time and date feature support in the new driver.
It's about time.
That is, this first post is about the time SQL Server data type. If you have been using the java.sql.Time class in your JDBC application, you're probably well aware by now that until SQL Server 2008, there was no corresponding time data type in SQL Server. If you had time-only data, maybe you converted it to a java.sql.Timestamp or a String before using it in a query. Or maybe you just used setTime/updateTime with the datetime or smalldatetime SQL Server data type and let the JDBC driver do the conversion for you.
Well, there's good news. You can now use setTime/updateTime with time SQL Server data type. But it's not quite as simple as just changing your table schemas to use the time SQL Server data type...
The reason it's not quite so simple has to do with backward compatibility. Perhaps you are one of those folks who currently use setTime/updateTime with datetime or smalldatetime columns. You may have noticed that values set that way are stored with the date 1/1/1970 (January 1st 1970). That's because the JDBC driver filled in the date part (1/1/1970) itself before sending the value to the server as a datetime. Other Microsoft SQL Server data access technologies, and SQL Server itself, use the date 1/1/1900 for the same conversion. And that leads to a small problem. If the new driver's setTime/updateTime were to send the value as a time rather than as a datetime, then SQL Server would use the 1/1/1900 date when converting the value to a datetime/smalldatetime. That could lead to a mix of 1/1/1900 and 1/1/1970 dates in those datetime/smalldatetime columns that your application thinks of as times, which you might consider to be a bad thing... So, "out of the box" the 3.0 driver preserves the behavior of drivers past, which is that setTime/updateTime sends a datetime to the server with a date component of 1/1/1970.
But surely it makes sense for setTime/updateTime to use the time data type rather than the datetime data type! You don't want to be stuck with the old behavior forever. Or maybe you're doing new development and you don't want to be stuck with the old behavior at all. Maybe you've used a parameterized query like:
SELECT * FROM myTable WHERE myTime = ?
and received an exception like:
"The data types datetime and time are incompatible in the equal to operator. "
What's wrong? Nothing really. You just need to tell the driver to use the time data type rather than the datetime data type with setTime/updateTime. You do that by setting the connection property:
sendTimeAsDateTime=false
You can do that in the connection URL, or using SQLServerDataSource methods.
Just be aware that if you set sendTimeAsDateTime=false and you use setTime/updateTime with datetime and smalldatetime columns, you may end up with a mix of 1/1/1900 and 1/1/1970 dates in those tables.
Other than that, using the new time data type is pretty straightforward.
At least as long as you only need millisecond precision with your times... If you want to take advantage of the SQL Server time data type's 100 nanosecond precision, you'll have to manipulate the value as a java.sql.Timestamp rather than a java.sql.Time because java.sql.Time does not have sub-millisecond precision. Manipulating the value as a java.sql.Time results in the value being rounded to the nearest millisecond.
That's it for today's post. The next post will cover storing timestamp and location information together in one value, using the datetimeoffset data type.
--David Olix [SQL Server]