Migrating from Oracle to Azure SQL caveat – java.sql.Date does not represent time.

My team recently migrated one of our main database from Oracle to Azure SQL database. In this and upcoming posts, I’m going to share a few things I have learned during the process. Specifically, in this post, I share an issue we ran into with storing date and time, after migrating the schema and data from our Oracle into azure SQL database.

Problem

Our oracle database contains tables which have columns of Date type. Values of the date columns originate from a java application, using oracle JDBC driver . In the database, the values contain both date and time information. After migrating the schema and data to azure SQL, and updating the app to connect to the new database using Microsoft JDBC Driver for SQL, new date values no longer contain time component, only the date.

Below simplified snippet demonstrates a typical way we used to set a date value via a prepared statement.

Connection conn = getConnection();
PreparedStatement stmt = conn.prepareStatement("Update DocumentTable.Document Set column_a = ?, date_added_on = ? where id = ?");
stmt.setString(1, "new value"); 
java.sql.Date dtAddedOn= new java.sql.Date(new java.util.Date().getTime());
stmt.setDate(2, dtAddedOn);
stmt.setInt(3, 123); 

In the above snippet, notice that dtAddedOn has the type java.sql.Date. The value contains both date and time info (e.g. 2022-05-27 9:43 PM). After insertion, using Oracle developer with configured date time format, the resulting value in the oracle database has this format: 2022-MAY-27 09:43:00 PM.

After migrating the schema and data, without changing codes in the java app besides changing the JDBC driver and connection string, the hour, minute, and second of the resulting value in the azure SQL is all 0: 2022-05-27 00:00:00.

Reason

By definition, SQL date does not contain time information. Since we use java.sql.Date to store the date as shown in the above snippet, when the Microsoft JDBC driver sends the statement to the database for execution, it sets all the time parts to 0.

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.

java.sql.Date

In fact, if you look at the class definition of java.sql.Date, you will see that all the methods and constructor that have to do with time have been deprecated.

Therefore, Microsoft JDBC driver behaves as expected by normalizing the time values to 0. However, I was intrigued as to why we are able to store both date and time when using the Oracle JDBC driver and Oracle database. It turns out the reason is because the Oracle JDBC driver converts the date value to java.sql.Timestamp before sending to the database.

Beginning with [Oracle JDBC 11.1], the driver maps SQL DATE columns to  java.sql.Timestamp by default

Displays same DATA_TYPE value for TIMESTAMP and DATE, while using ojdbc6 — oracle-tech

Solution

Once we understand the problem, the solution is simple. We update the app to use java.sql.Timestamp, which represents both date and time.

Connection conn = getConnection();
PreparedStatement stmt = conn.prepareStatement("Update DocumentTable.Document Set column_a = ?, date_added_on = ? where id = ?");
stmt.setString(1, "new value"); 
java.sql.Timestamp dtAddedOn = Timestamp.from(Instant.now());
stmt.setTimestamp(2, dtAddedOn);
stmt.setInt(3, 123); 

Notice the changes in the above snippet compared to the one at the beginning:

• We use java.sql.Timestamp instead of java.sql.Date.
• We use setTimestamp method on the prepared statement, instead of setDate

Upon making the change, executing the update statement results in the the date_added_on column in azure SQL database to contain both date and time as expected.

References

Difference Between java.sql.Time, java.sql.Timestamp and java.sql.Date in Java – GeeksforGeeks

java.util.Date vs java.sql.Date | Baeldung

Date (Java SE 11 & JDK 11 ) (oracle.com)

Using basic JDBC data types – JDBC Driver for SQL Server | Microsoft Docs

Oracle JDBC Frequently Asked Questions