Open Warrior

I had the problem of needing to populate a MySQL table with dates between openingDate and closingDate, using Java and JDBC Connector/J.
Well, after a while I figured out how to do it and thought I’d share it with you.

Step 1: See how many dates will need to be entered

ResultSet rs = statement.executeQuery(”SELECT DATEDIFF(’2007-07-01′, ‘2006-07-01′);”);
rs.next();
int days = rs.getInt(1);

The MySQL DATEDIFF query calculates the value of date1 – date2, in this case (2007-07-01 – 2006-07-01) which evaluates to 365.
The days variable takes the value of the first column returned by the ResultSet object, which is the result of the DATEDIFF query.

Step 2: Insert the days into the database

for (int i = 0; i <= days; i++){
int j = statement.executeUpdate(“INSERT INTO MyTable VALUES (’2006-07-01′ + INTERVAL ” + i + ” DAY);”);
}

This statement will need to be customized to suit your table but essentially what it does is takes the first date specified, and add 1 day for each count in the day variable.

At the end you have a table filled with dates!