Sunday, July 24, 2022

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 a 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 supposed 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 the JDBC driver could do the translation depending upon the data type of columns and Java developer could just pass the java.util.Date I mean, 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 that 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.





Things to remember about SQL and Normal Date in Java

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 violets 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 like toSQLDate(), toSQLTime(), etc. 


Further Reading
If you are curious to learn more about date and time in Java, You might like the following articles and tutorials:
  • 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)

By the way, if you are using Java 8 then consider using LocalDate and LocalTime which are both immutable and thread-safe but yes again converting LocalDate to SQL Date or LocalTime to SQL date is not easy and I will show you how to do it on next article, till then keep learning. 

2 comments :

SARAL SAXENA said...

@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.setTime(utilDate);
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