Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

SQL and Java Go on a Date

By Christoffer Hedgate,

Background

Lately I've been working on a Java application, specifically a search engine implementation, where I needed to work with data from a SQL Server database. The company I work for have a Java based platform for implementing search engines and I've worked with it much before, but this was the first time that the search engine I was implementing was to index and search data stored in a SQL Server database. Working with one of the indexes I encountered some problems and learned a few experiences, so I thought I'd share these in this article. The index in question was one that contained dates, i.e. a column with data type datetime in SQL Server.

Dates in Java

In Java, data of type date is stored in objects of the class Date. The normal way to create an instance of one is to supply the constructor with a value of type long, which represents the number of milliseconds that have expired since 1970-01-01 00:00:00. However, when users would use the search engine they would supply dates in the format above, i.e. yyyy-mm-dd hh:nn:ss (ANSI standard format and also the way we write dates in Sweden). So the first thing I needed to do was to convert these strings and the above format into dates. Using a nice programming language such as Java this is easy. The code snippet below shows how to do this:
 
  String s = new String("2003-08-30 16:00:00"); 
  SimpleDateFormat sdf = (SimpleDateFormat) SimpleDateFormat.getDateTimeInstance(); 
  sdf.applyPattern("yyyy-MM-dd HH:mm:ss"); 
  Date d = sdf.parse(s); 

SQL Server datetime

Now that I had code that converted the user supplied date to search for I needed to write a T-SQL query that was to be run to populate the index in the search engine. When the search engine starts up it populates all indexes and then all queries run against it are only run inside the search engine, the database (or whatever data store is being indexed) is not used again until the indexes are repopulated or updated. Since dates are stored as number of milliseconds since 1970-01-01 I thought it would be a good idea to convert the dates in SQL Server the same way in my T-SQL query. Using the datediff-function, I tried this query:
 
  SELECT DATEDIFF(ms, '1970-01-01 00:00:00', datecolumn) AS numberofms 

Overflow error

This didn't work to well though. As most of you probably know, the datediff-function returns an int, and the int data type in SQL Server can represent numbers from -2^31 (-2,147,483,648) through 2^31 - 1 (2,147,483,647). This means that the greatest difference between the two days that can be represented by a number of milliseconds (using an int) is 24 days, 20 hours, 31 minutes and 23.647 seconds. As soon as the dates in the datecolumn where larger than that I would get an overflow error. So I needed a way to represent the number of milliseconds with a bigint instead, as this data type supports much bigger numbers. I was surprised to find that SQL Server does not have a function for doing this, like a datediff_big (compare with count and count_big). Instead I had to convert it myself. The way I came up with was to first use datediff to get the difference in number of minutes, convert them to milliseconds and then add the seconds part of the date and finally add the milliseconds part of the date. This is the T-SQL query I ended up with:
  SELECT CAST(DATEDIFF(n,'1970-01-01 00:00:00',datecolumn) AS bigint) * 60 * 1000 + DATEPART(s,datecolumn) * 1000 +
DATEPART(ms,datecolumn) AS numberofms

UTC dates

So, all is well, right? Not quite. When testing I noted that the dates (represented by long values in my Java application) that I had converted in Java where different from those I got from SQL Server, even when they where created using the same date in string format. I found out that Java by default stores it's dates in UTC time (Universal Time Coordinate). SQL Server however does not, so I once again started looking through Books Online for a nice function to convert a datetime value to a UTC datetime. No luck. SQL Server does have one function that returns the current date and time in UTC time, getutcdate, but that didn't help me because I needed to convert the stored values in datecolumn. Next thought was to find out what to add or subtract from my dates to convert them into UTC time. This proved to be difficult though because in UTC about once every year or two there is an extra second, called a "leap second." And I couldn't just use getutcdate and getdate and compare the difference between them as that would mean that I would have to reset the system time to whatever date and time I needed to compare for each value in datecolumn. Since leap seconds are added 'all the time' the difference between two dates in UTC time and 'normal' time is not the same for say 1970-01-01 and 2003-08-30.

Back to Java

Once again I found the answer in Java with all the help it provides being such a rich programming language. Assuming that the computer running SQL Server and the computer running SQL Server is configured to use the same time zone I can use the SimpleDateFormat object to get the current time zone, then use the function getOffset. This function returns the offset of this time zone from UTC at the specified date (represented by a number of long data type). So I revised the Java code from above to the one below, and I was all set!
 
  String s = new String("2003-08-30 16:00:00");
  SimpleDateFormat sdf = (SimpleDateFormat) SimpleDateFormat.getDateTimeInstance();
  sdf.applyPattern("yyyy-MM-dd HH:mm:ss");
  Date temp = sdf.parse(s);
  Date d = new Date(temp.getTime() + sdf.getTimeZone().getOffset(temp.getTime()));

Summary

In this article I have discussed my experiences with working with dates in Java and SQL Server. I hope to come back to it in the future and do a similar comparison in .Net, or even using SQL Server Yukon. Being under an NDA I can't say anything about Yukon, but I can say that I wouldn't have implemented my solution exactly as above if I was using Yukon instead of SQL Server 2000 as my data storage.
Total article views: 11567 | Views in the last 30 days: 1
 
Related Articles
FORUM

convert number into words

convert number into words in sql server 2005

FORUM

Convert exponential Number to non-exponential numbers...

Convert exponential Number to non-exponential numbers...

FORUM

Convert GETDATE() to NUMBER

SQL to convert date to number

FORUM

convert month name to month number

convert month name to month number

FORUM

Convert ASCII strings to the integer groups of numbers?

SQLServer2005, ASCII to number value convertion

Tags
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones