CODEWITHSUNDEEP
javasql-serverdatedatetime
mufc
mufc

Reputation: 715

Inserting date into SQL server from Java - not formatting properly

I am trying to insert a date into a SQL table in a specific format. I am successfully inserting the date into a column of type datetime, but the formatting is incorrect.

I have the following:

TimeZone tz = TimeZone.getTimeZone("UTC");
DateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS");
df.setTimeZone(tz);
String timeString = df.format(System.currentTimeMillis());
Date date = df.parse(timeString);
java.sql.Date sqlDate = new java.sql.Date(date.getTime());

I can see that timeString is being set correctly to a string like : "2017-01-11 18:19:06.662"

but date then gets set to something like: "Wed Jan 11 13:19:06 EST 2017" (missing milliseconds)

and sqlDate gets set to "2017-01-11"

when I look at the table to see what the value is, I see that for the field I'm setting, I'm seeing that the datetime gets set to "2017-01-11 00:00:00.000".

Now this is missing all of the time info, which I'm sure has to do with the fact that sqlDate gets set to a value with no time, but Im not sure why sql date has no time value. I looked at some older posts that tried dealing with the same, but found no resolution here or here.

Suggestions on why this may be happening are appreciated.

Upvotes: 1

Views: 8852

Answers (1)

Nicolas Filotto
Nicolas Filotto

Reputation: 44965

Your code seems to be correct, you only get confused due to the implementation of toString:

  1. In java.util.Date which gives the date to the form dow mon dd hh:mm:ss zzz yyyy.
  2. In java.sql.Date which gives the date to the form yyyy-mm-dd.

But if you compare what getTime() returns for both (date and sqlDate), you will get the same value as what you initially provided to df.format to get timeString.

Your mistake is more that you use java.sql.Date for a datetime because as stated into the javadoc:

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.

You are supposed to use java.sql.Timestamp instead of java.sql.Date to keep the time part of your date (hours, minutes, seconds, and milliseconds) as you expect.

Your code should then be:

...
java.sql.Timestamp sqlDate = new java.sql.Timestamp(date.getTime());

Upvotes: 3

Related Questions