11

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.

Improve this question
4
  • 2
    print the query content, and then try to run that in mssql directly (for example using sql server management studio). Commented Jan 28, 2013 at 8:42
  • value of "cal" is most probably too large for sql server to handle it. Commented Jan 28, 2013 at 8:43
  • 3
    You should use a preparedstatement docs.oracle.com/javase/6/docs/api/java/sql/…. Commented Jan 28, 2013 at 8:46
  • Don't format it, check out this constructor docs.oracle.com/javase/6/docs/api/java/sql/… Commented Jan 28, 2013 at 8:49

6 Answers 6

Reset to default
23

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();
Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

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.
You do not recieve ResultSet after insert records. You may only monitor updated rows count.
How do you execute statement? Show please exception stack trace.
Basically, it is the other problem that I have posted: stackoverflow.com/questions/14525690/…
3

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.

Improve this answer

2 Comments

need to setTimestamp() because column has Datetime type.
I am reading now about SQL injection. I hadn't realised that it may cause so many problems when I use string concatenation.
1

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.

Improve this answer

Comments

0

This is because you are trying to save String date value to Date type DB field.

convert it to Data dataType

You can also use the datetime "unseparated" format yyyymmdd hh:mm:ss

Improve this answer

1 Comment

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

You could use Joda framework to work with date/time. It maps own date/time types to Hibernate/SQL types without problem. When you set parameters in HQL query joda carries about right type mapping.

Improve this answer

1 Comment

but he is not using hibernate, but JDBC
0

If you want to store current date and time then you should use MYSQL inbuilt method NOW(). for brief documentation refer http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html . so your code will be like.

INSERT INTO Companies CreatedOn VALUES(NOW())"

However If you want to do it using java Date-util then it should be

Calendar cal = Calendar.getInstance(); 
java.sql.Timestamp timestamp = new Timestamp(cal.getTimeInMillis());
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.