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. 

blog comments powered by Disqus