JDBC driver for SQL Server 2005

Question

JDBC driver for SQL Server 2005

Ujjwala Nayampalli-479426

Grasshopper

Points: 16
More actions
August 20, 2007 at 3:00 am

#178130

I am using SQL Server 2005 as the backend for my application developed in Java. We are using sqljdbc diver to connect to the database.
My INSERT statement uses DateTime values of the format "dd/mm/yyyy". I am calling set dateformat dmy before executing the insert statement. This works perfectly fine from SQL Query Analyser. However when I excute it from my application, I get the following error message:
Arithmatic overflow while converting expression to data type datetime.
I did not have any issues when I was using SQL Server 2000 with mssql driver.
When I was searching on the net for this issue, somewhere it was mentioned that set dateformat call might be ignored and I should not rely on it and should instead use the convert function before passing the dates. In this case I will have to make lot of changes in my code!
Code snippet:
sqlQuery
= "set dateformat dmy";
ps= con.prepareStatement(sqlQuery);
ps.execute();
sqlQuery = "UPDATE <tablename> set startdate=?"
ps
= con.prepareStatement(sqlQuery);
ps.setString(1,'30/08/2007');
ps.execute()

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

devaraya Default port Points: 1455 More actions · Answer 1

Hi Ujjwala,

I hope this is not with the jdbc driver. U check down your code suppose the field u selected for capturing date values is varchar the length may be problem. I hope this... not sure.

best of luck

Ramaa

Ujjwala Nayampalli-479426 Grasshopper Points: 16 More actions · Answer 2

Hi Ram,

Thanks for the reply.

Data type of that field is "datetime" and it has got the default size i.e 8

The code is working fine with SQL Server 2000. In context with the code snipette I posted, if i give the date as '01/08/2007' (d/m/y) it gets inserted in the database but as 8th January 2007 rather than 1st August 2007 although before inserting I have set the dateformat as dmy by calling

set dateformat dmy