java.sql.Date with example. Convert java.util.Date to java.sql.date and vice versa. Differences among util.Date, sql.Date / Time / Timestamp

Interview questions like How to convert java.util.Date to java.sql.date and Difference between java.util.Date and java.sql.date may be asked . This tutorial answers the above interview questions and also explains why we need Java.sql.Date in java . Also covers mapping of java date types to sql date types. and the differences among java.util.Date, java.sql.Date / Time / Timestamp
java.sql.Date
Before go to java.sql.Date , let us discuss about three JDBC types related to date and time .
JDBC Date/Time types
1. JDBC DATE - represents a date only (ie. date consists day, month, and year)
2. JDBC TIME - represents a time only (ie. consists of hours, minutes, and seconds)
3. JDBC TIMESTAMP - represents DATE and TIME plus a nanosecond field.
The corresponding SQL DATE , SQL TIME and SQL TIMESTAMP type are defined in SQL-92 , the third revision of the SQL standard., but it is implemented by less number of databases. Some databases offer alternative SQL types for date , time and timestamp
Why we need java.sql.Date type
We can't map java.util.Date class with any of the three JDBC date/time types because java.util.Date includes both DATE and TIME information but has no nanoseconds (JDBC DATE requires only date component , TIME requires only time component , TIMSTAMP requires date+time+nanoseconds , thus util.Date is not matched with any of these types ) . So we need some other date class to map with JDBC date / time types. JDBC defines three classes (java.sql.Date , java.sql.Time, java.sql.Timestamp) that extends java.util.Date class , to correspond to the JDBC types for storing date and time information . Now let us see the mapping of java date type to SQL date types. .

1. java.sql.Date type maps to SQL DATE type : It includes only date component . The time component should be set to zero (ie. hour, minute, second, and millisecond fields of the java.util.Date base class should be set to zero) . How the sql date is calculated ? . java.sql.Date constructor accepts milliseconds value that represents the number of milliseconds passed since January 1,1970 00:00:00.000 GMT. If the milliseconds supplied to the constructor is negative, the date is calculated as the number of milliseconds before January 1, 1970. Otherwise, the date is computed as the specified number of milliseconds after January 1, 1970.

2. java.sql.Time type maps to SQL TIME type : It includes only time component . The date component should be set to zero . ( i.e. year, month, and day fields of the java.util.Date base class are set to 1970, 0 (January) , and 1 which is zero date in java)

3. java.sql.Timestamp type maps to SQL TIMESTAMP type : It includes date + time + nanoseconds. This class extends java.util.Date by adding a nanoseconds field.

Some of the methods of java.sql.Date
Date (long date ) - Constructs a Date object using the given milliseconds time value
valueOf(String s) - Converts a string in JDBC date escape format (yyyy-mm-dd) to Date value (java.sql.Date) . Throws IllegalArgumentException if the date given is not in the format yyyy-mm-dd
toString () - Formats a date in the date escape format yyyy-mm-dd. Use "yyyy.MM.dd" format instead of "yyyy-mm-dd" in SimpleDateFormat to specify date format. "mm" indicates minutes rather than the month
getHours() , getMinutes() , getSeconds() - throws java.lang.IllegalArgumentException() , because java.sql.Date does not have time (hour, minute , seconds)

Differences among java.util.Date, java.sql.Date , java.sql.Time and java.sql.TimeStamp :
java.util.Date - includes both DATE (year, month, day) and TIME (hour, minute, second, and millisecond) information but does not have nanoseconds . Calling constructor with no values (Date()) returns
milliseconds representing time.
java.sql.Date - includes only the Date component. The time component should be set to zero (ie. the hour, minute, second, and millisecond fields of the java.util.Date base class should be set to zero) . Calling constructor with no value ( new java.sql.Date()) throws exception.
java.sql.Time - includes only the Time . (i.e. hour, minute, second, and millisecond) component. The date components should be set to the zero (i.e. year, month, and day fields of the java.util.Date base class are set to 1970, 0 and 1 which is zero date in java)
java.sql.TimeStamp – stores both Date and the Time (hour, minute, second, millisecond ) plus and nanoseconds

JDBC supports the ISO date escape sequences as follows date - yyyy-mm-dd , time - hh:mm:ss , timestamp - yyyy-mm-dd hh:mm:ss.ms.microseconds.ns . java.sql.Date returns the date in the same format i.e JDBC date escape format (yyyy-mm-dd)

Conversion
To covert java.util.Date to java.sql.Date , Pass the milliseconds time value to the constructor of java.sql.Date class (i.e. Date(long date) ) . The java.util.Date class has the method getTime() which returns milliseconds time value . The UtilDateToSqlDate method given below accepts util.Date and returns sql.Date.
Similarly , to convert java.sql.Date to java.util.Date , pass the milliseconds time value to the constructor of java.util.Date class which returns util date . The SqlDateToUtilDate method accepts sql date and returns util date.
The follwing code has two static methods to convert util date to sql date and vice versa
  
import java.sql.Date;
class sqlDate
{
public static void main(String args[])
{
java.util.Date date1 = new java.util.Date();
System.out.println("Util Date : "+ date1);
//util date to sql date
java.sql.Date date2 =UtilDateToSqlDate(date1);
System.out.println("Sql Date : " + date2);
//sql date to util date
java.util.Date date3= SqlDateToUtilDate(date2);
System.out.println("Back to Util Date : "+ date3);
}

//Method to convert java.util.Date to java.sql.Date

public static Date UtilDateToSqlDate(java.util.Date utilDate) {
Date sqlDate = null;
if (utilDate != null) sqlDate = new Date(utilDate.getTime());
return sqlDate;
}


// Method to convert java.sql.Date to java.util.Date

public static java.util.Date SqlDateToUtilDate(Date sqlDate) {
java.util.Date utilDate = null;
if (sqlDate != null) utilDate = new java.util.Date(sqlDate.getTime());
return utilDate;
}


}

Output


For More example using java.sql.Date / Timestamp with database , Please visit my earlier post Storing date / timestamp value in database

4 comments:

Akhil S said...

nice post keep posting...
http://tech-labz.blogspot.com/

Hung Huynh said...

as far as saving date in database, using a 'long' might be preferred

Javin Paul said...

Nice and clear, it well highlight the main difference that sql date can not represent time details. you can also see SQL date vs Utils Date in Java for more differences.

ganapathi pudi said...

with hours and minitus

Post a Comment

 
Copyright 2011 Java Online