DateTime format during INSERT using MS JDBC driver

Hi,
Using the latest MS JDBC-driver:
The date format of the SQL Server causes me some trouble at the moment:
My INSERT statement uses DateTime values of the format "yyyy-mm-dd
hh:mm:ss.mmm" but they somehow get interpreted as"yyyy-dd-mm" causing a date
format out of range exception.
How can I "force" SQL Server to use my date format? Calling "SET DATEFORMAT
YMD" on each open connection before the statement is executed does not help
but this may be caused by additional connections being opened "behind the
scenes" (so I've been told). I'm not sure this is so even if my connection
pool keeps connections open.
The db-user opening the connection has the correct language setting and the
collation label of the databases is also correct.
So I'm wondering whether it's the regional setting of the Windows account
that runs the SQLServer-service (or SQLSERVERAGENT-Service?) that causes
this? But then - what region would use "yyyy-dd-mm" as it's date format?
Or is there a setting on the JDBC-driver that can modify this behaviour?
- Tim
Hi Tim,
You can either use the parameterized query and pass in a value of a
Java.Sql.Date type. You can thus convert the "yyyy-mm-dd" string to Date.
Below is the code snippet:
PreparedStatement st = connection1.prepareStatement("INSERT
Customers (ArchiveDate) VALUES (?)");
st.setDate(1, Date.valueOf("1999-01-30"));
st.executeUpdate();
Or if you choose to hard code the "yyyy-mm-dd" in the query string, you can
use CONVERT function to convert the string to a datetime sql data type
according to the proper style of date format. 120 is the ODBC canonical
style that converts yyyy-mm-dd hh:mi:ss(24h). Below is the code snippet:
PreparedStatement st = connection1.prepareStatement("INSERT
Customers (ArchiveDate) VALUES (CONVERT (datetime, '1999-01-30', 120) )");
st.executeUpdate();
For more info on CONVERT, please refer to the SQL Server Books Online.
Yilei