Java.util.date To Java.sql.date Doesn't Contain Time

March 22, 2024 Post a Comment

I'm getting a SqlException on a PreparedStatement for violating a uniqueness constraint on a table (dupe key). Essentially my table looks like this: mytable ======= mytable_id

Solution 1:

Use

ps.setTimestamp(new java.sql.Timestamp(System.currentTimeMillis());

See this: Using setDate in PreparedStatement

Solution 2:

The accepted answer is correct. I'll add an explanation.

Confusing Hack

In SQL, a DATE is a date-only value lacking a time-of-day or time zone.

The mess that is the old date-time classes bundled with Java lack any such class to represent date-only values.

Instead, the Java team created a hack. They created java.sql.Date by extending java.util.Date which, despite its confusing name, has a date portion and a time-of-day portion. For the SQL-oriented subclass, they set time-of-day portion to zero values. You might think of that as "midnight". So a java.sql.Date has a time-of-day but pretends not to.

To quote the java.SQL.Date doc:

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.

For a date-and-time value in SQL use the java.sql.Timestamp class as shown in accepted answer.

java.time

These poorly designed date-time classes have been supplanted by the new java.time package built into Java 8 and later. This package was inspired by the Joda-Time library.

The Java.time package includes classes to represent date-only and time-only classes. Eventually the JDBC drivers will be upgraded to directly support the new data types. Until then, use the several conversion methods added to the old and new classes. Search StackOverflow.com for many examples and discussion, as this Question is largely a duplicate.

Solution 3:

java.sql.Date extends java.util.Date but works differently: In SQL, DATE has no time. Therefore, the Java object also ignores hours, minutes, etc.

Try java.sql.Timestamp instead but you may need a cast (in SQL) to convert it to DATETIME.

mssql 2005 datetime and jdbc

Solution 4:

Instead of setDate you need to try setTimestamp

java.sql.Date - A dateonly (notime part)
java.sql.Time - A timeonly (nodate part)
java.sql.Timestamp -Bothdateandtime

Lab Sql