Share this page 

Handle datesTag(s): JDBC


To get the current date in SQL format. 
java.util.Date today =
        new java.util.Date();
java.sql.Date sqlToday =
   new java.sql.Date(today.getTime());
For Timestamp, it's the same idea 
java.util.Date today =
        new java.util.Date();
java.sql.Timestamp now =
    new java.sql.Timestamp(today.getTime());
To use a Date, Time or Timestamp in a query, you can use JDBC escape codes. 
Date       {d 'yyyy-mm-dd'}
Time       {t {'hh:mm:ss'}
Timestamp  {ts `yyyy-mm-dd hh:mm:ss.f . . .'}
note: the .f .... is optional
For example, a Statement with a Date will look like this 
java.util.Date today =
        new java.util.Date();
java.sql.Date sqlToday =
        new java.sql.Date(today.getTime());

String query =
   "select * from cust where purchase_date < { d '" 
       + sqlDate.toString() + "' }");
With a PreparedStatement, you don't need JDBC escape codes, the JDBC driver will do the job for you. 
java.util.Date today =
        new java.util.Date();
java.sql.Date sqlToday =
        new java.sql.Date(today.getTime());

PreparedStatement p = theConn.prepareStatement
   ("select * from cust where purchase_date < ?");
p.setDate(1, sqlToday);
ResultSet rs = p.executeQuery();
To INSERT 
PreparedStatement p = theConn.prepareStatement
    ("insert into TableWithADateColumn values(?)");
p.setDate(1, sqlToday);
p.executeUpdate();
or 
p.executeUpdate
("insert into TableWithADateColumn values( { d '1999-12-31' } )");

One thing to remember when using java.sql.date is (according to the javadoc) : 

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.
mail_outline
Send comment, question or suggestion to howto@rgagnon.com