August 20, 2007 at 3:00 am
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()
August 20, 2007 at 5:49 am
Ramaa
August 20, 2007 at 6:30 am
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
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply