Java: Insert into a table datetime data

Question

I am trying to insert into a variable in MS- SQL database the current date and the time. I use this format:

DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS"); 
Calendar cal = Calendar.getInstance();  
System.out.println(dateFormat.format(cal.getTime()));

and I get this as a result 2013-01-28 09:29:37.941

My field in the database is defined datetime and as I have seen in other tables which have the same field, the date and the time is written exactly like this 2011-07-05 14:18:33.000.

I try to insert into the database with a query that I do inside a java program, but I get this error

SQL Exception: State : S0003 Message: The conversion of a varchar data type to a datetime data type of the value is out of range. Error : 242

My query is like that:

query = "INSERT INTO Companies CreatedOn"+ 
         "VALUES ('" + dateFormat.format(cal.getTime()) + "')"

but I don't understand what I am doing wrong.

Solution 1

According to the error description, you are inserting an incorrect type into the database. See JDBC to MSSQL. You should convert Calendar to Timestamp.

Try using:

PrepareStatement statement 
    = connection.prepareStatement("INSERT INTO Companies CreatedOn VALUES(?)");
java.sql.Timestamp timestamp = new java.sql.Timestamp(cal.getTimeInMillis());
statement.setTimestamp(1, timstamp);
int insertedRecordsCount = statement.executeUpdate();

Solution 2

First of all, do NOT use string concatenation. Have you ever heart about SQL injection?

Correct way how to do that is to use prepared statement:

Idea is you define statement with placeholders and than you define value for those placeholders.

See @Taky's answer for more details.

Solution 3

dateFormat#format this method returns formatted string not Date object. Database field is DateTime and it is expecting java.sql.Timestamp to be inserted there not String according to docs.

To conform with the definition of SQL DATE, the millisecond values wrapped by a java.sql.Date instance must be 'normalized' by setting the hours, minutes, seconds, and milliseconds to zero in the particular time zone with which the instance is associated.

Try java.sql.Timestamp object instead of String in query and I'd recommend you to use PreparedStatement.

Comments

[Dariusz]

Nope, the error message states that the problem is elsewhere - I posted the same response and then deleted it ;)

[Taky]

need to setTimestamp() because column has Datetime type.

[Betlista]

but he is not using hibernate, but JDBC

[Dimitra Micha]

I am trying to do that, but then some other problems come up. "There was no result set returned by the statement.", which I think is because I am doing smth really wrong in my program.

[Taky]

You do not recieve ResultSet after insert records. You may only monitor updated rows count.

[Taky]

How do you execute statement? Show please exception stack trace.

[Dimitra Micha]

Basically, it is the other problem that I have posted: stackoverflow.com/questions/14525690/…

[Dimitra Micha]

I am reading now about SQL injection. I hadn't realised that it may cause so many problems when I use string concatenation.