Monday, October 2, 2017

How to convert java.util.Date to java.sql.Date - JDBC Example

You often need to convert java.util.Date to java.sql.Date if you are storing dates in database e.g. SQL SERVER or MySQL. Since JDBC has their own data types for date and time e.g. java.sql.Date, java.sql.Time and java.sql.TimeStamp to match with database date, time and date-time types, you cannot pass a java.util.Date directly. All methods which are suppose to store dates e.g. setDate(paramName, paramValue) expects java.sql.Date, so it becomes essential to know how to convert java.util.Date to java.sql.Date in JDBC. You would be surprised to know that java.sql.Date is a subclass of java.util.Date and all it does is suppress or remove time-related fields from java.util.Date.

It is also a good example of a class which violates Liskov substitution principle, because even though java.sql.Date extends java.util.Date, you cannot pass around it to the method which expect java.util.Date because all time-related methods e.g. getHour(), getMinute() and getSeconds() method throws java.util.NotSupportedException

I really hope that JDBC driver could do the translation depending upon the data type of columns and Java developer could just pass the java.util.Date e.g. convert it to java.sql.Date if the column in the table is of type DATE, java.sql.Time if the type of column is TIME and java.sql.Timestamp if the data type of column is DATETIME.

Converting java.util.Date to java.sql.Date - Example

Unfortunately, there is no method like toSQLDate() in java.util.Date class to facilitate conversion between util date and SQL date but you can use getTime() method to extract long millisecond value from java.util.Date and create a new java.sql.Date based on that value as shown below:

Date now = new Date();
java.sql.Date sqlDate = new java.sql.Date(now.getTime());

This is the easiest and right way to convert a java.util.Date to java.sql.Date in Java. To learn more about JDBC and how to write Java application which connects to the database, please see Core Java, Volume II--Advanced Features (10th Edition), one of the best books to learn advanced features of Java programming language.

How to convert java.util.Date to java.sql.Date in JDBC

Things to remember

1) java.sql.Date mapped to DATE datatype on JDBC i.e. Type.Date, which corresponds to date equivalent in DB side. In SQLSERVER till 2008 it maps to DATETIME and afterward maps to DATE data type.

2) java.sql.Date extends java.util.Date but voilates Liskov Substituion Principle.

3) You can convert between java.util.Date and java.sql.Date by using getTime() method.

4) Here is mapping between MySQL date and time types and Java date types:

How to convert java.util.Date to java.sql.Date with example

That's all about how to convert java.util.Date to java.sql.Date in Java and JDBC. It's one of the most useful tips while working in JDBC. You can even create a class like JdbcUtil to host all date and time-related conversion methods e.g. toSQLDate(), toSQLTime() etc.

Further Learning
Complete Java Masterclass
Java Fundamentals: The Java Language
Java In-Depth: Become a Complete Java Engineer!

Further Reading
If you are curious to learn more about date and time in Java, You might like following articles and tutorials:
  • Java Advanced Feature Volume 2 By Cay S. Horstmann (book)
  • 10 Examples of Java 8 Date and Time API (tutorial)
  • How do you calculate the difference between two dates in Java? (solution)
  • How to convert a String to Date in Java? (example)
  • How to get a day, month and year from a Date in Java? (solution)
  • How to convert XMLGregorianCalendar to Date in Java? (solution)
  • How to get the current date with Timezone in Java? (example)
  • 3 ways to compare two dates in Java? (program)
  • How to add days, months and years from a Date in Java? (solution)
  • The difference between java.sql.Time, java.sql.Timestamp and java.util.Date in Java? (answer)
  • How to increment the date in Java? (solution)
  • How to convert local time to GMT in Java? (answer)
  • How to convert Date to String in Java? (answer)
  • Why should you not use SimpleDateFormat in Java? (answer)



@javin what about this alternative approach dude ..

java.util.Calendar cal = Calendar.getInstance();
java.util.Date utilDate = new java.util.Date(); // your util date
cal.set(Calendar.HOUR_OF_DAY, 0);
cal.set(Calendar.MINUTE, 0);
cal.set(Calendar.SECOND, 0);
cal.set(Calendar.MILLISECOND, 0);
java.sql.Date sqlDate = new java.sql.Date(cal.getTime().getTime()); // your sql date
System.out.println("utilDate:" + utilDate);
System.out.println("sqlDate:" + sqlDate);

Javin Paul said...

Hello @Saral, It does exactly same, but require more line of code :-) When you convert java.util.Date to java.sql.Date by passing the time since epoch, it automatically suppress time-related fields.

Post a Comment