The Design of Software (CLOSED)

A public forum for discussing the design of software, from the user interface to the code architecture. Now closed.

The "Design of Software" discussion group has been merged with the main Joel on Software discussion group.

The archives will remain online indefinitely.

java and sql server

i am in the process of porting my app to run with ms sql server and i am having a problem with date time column.

my app is having problem in parsing value from datetime column becuse of 3 millisecond precision. since i do not need millisecond precision, i moved the data into smalldatetime data type column.

so if i run a query in sql analyzer, the value returned is precise upto second.

but if i run the same query in java, the value returned with getstring is precise up to i milli second. that is  the format is

yyyy-mm-dd hh:mm:ss.s

and my app is failing to parse it.

another issue is i cannot add format /convert etc in sql statements as its huge code change and we plan to support mutliple databases inaddition to our current database.

we thought it could be driver issue but another jdbc driver also produced the same result.

i am confused as such how could there be two different display of same value in two different clients(java and enterprise manager). and how do we solve this on db side?
or is there a setting to turn off millisecond precision for datetime types?

thanks
bharat

Wednesday, April 19, 2006
 
 
Why are you parsing it as a string? Just return it as a real date and use it that way. getString is for strings, not dates.

And on the flip side, don't take a date and place it directly into a sql statement. Add a parameter for it and pass it a true Date value.

Wednesday, April 19, 2006
 
 
When you get data out of a JDBC driver you are getting a java.sql.Date value which has milli/nanoseconds appended on it. A smalldatetime is not the answer, that has nothing to do with the precision of the date, it just makes the range of dates you can support smaller.

First of all when writing queries you should use JDBC prepared statement parameters instead of converting dates to strings.

Also if you are checking for date equality (or date and time equality) you should never use Java equals method or its equivalent in MS SQL. The reason is that you usually are really not interested in exact equality but only partial equality.

For example given two dates:
04/19/2006 12:00:00.000 AM
04/19/2006 12:00:00.001 AM

Using Java equals or SQL equals these dates are not equal to each other. So if you only care if the day, month and year are the same you have to specifically check date, month and year only. So you have to write code to say day1=day1 and month1=month2 and year1=year2. You can do this in Java using a Calendar object, or you can do this in SQL server using the Year, Month, Date functions.

Same goes for if you care about the times you need to check hours, minutes and seconds in addition to date, month and year. You can leave off whichever part of the check you don't care about, which in your case is the milli/nanoseconds at the end.

Also when running queries that need to only get data for a specific date you do the following:

WHERE date >= '04/19/2006 12:00:00 AM' AND date < '04/20/2006 12:00:00 AM'

This will find all the records that occurred on 4/19 regardless of what time they occured on.
Had this problem before Send private email
Thursday, April 20, 2006
 
 
thank you both of you in making concepts clearer.

the biggest stumbling block is its a legacy app and our 99.99 % of our customers will still be using our default database.

its just that now we want to support other databases and i am working on compatibility issues where in i don't have to make  major codes changes.

so redesigning the app is ruled out nor we writing code is a good idea.

this may not be the correct solution but since i am using open source jdbc driver, i changed the source code so as to return only upto seconds and drop off any data after decimal.
( this was suggested by the driver developer)


thanks

Thursday, April 20, 2006
 
 
had this problem before,
  once we assemble the data on java side, it is returned to the client ( desktop app), where this data is formated and displayed.

thanks

Thursday, April 20, 2006
 
 
Have you tried using the Microsoft JDBC driver? They have a new one that was recently released.

Thursday, April 20, 2006
 
 

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics
 
Powered by FogBugz