Days elapsed since December 31, 1899

There seems to be a common practice to store dates into a database as days since January 1, 1900. The argument advanced is that it's done for performance reasons. The code shown here converts a Java date object to the number of days which have elapsed since that given date.

/**
 * Accepts a Date object from which the number of days from 1899-12-31 is then
 * which select upon table columns containing dates stored with this value.
 * The value in columns such as this is actually the number of days from 1899-12-31.
 * This method of storing dates can improve database performance, however, during
 * research or manual selection of records using a DB tool, the analyst must know
 * to use values such as 38700 instead of 2005-12-15 when formatting the SQL
 * statement.
 *
 * @param date
 * @return int days
 */
 public int getDaysSince1900 (Date date) {

   //Tested against the follwing SQL statement:
   //select  date = DATEDIFF(day, '1899-12-31', '2005-12-15') = 38700
   //Today's date at time of test was 2005-12-15 yielding 38700 as diff
   Date floorDate = new GregorianCalendar(1899,11,31,00,00).getTime();
   long diff = date.getTime() - floorDate.getTime();
   long daysSince1900 =  diff /(1000*60*60*24);

   //Convert to int (Stored in db as int)
   String ds = Long.toString(daysSince1900);
   int days = Integer.parseInt(ds);
   return days;
}


/**
 * Accepts an integer value representing the number of days from 1899-12-31 and
 * converts it into a Date object.
 *
 * @param days
 * @return dateSince1900
 */
public Date getDateSince1900 (int days) {
       
   Date floorDate = new GregorianCalendar(1899,11,31,00,00).getTime();
   long floorAsMilliseconds = floorDate.getTime();

   long millisecondsInDay = (1000*60*60*24);
   long daysAsMilliseconds = days * millisecondsInDay;

   Date dateSince1900 = new Date( floorAsMilliseconds + daysAsMilliseconds );

   return dateSince1900;        
}

The following will take a formatted date string and convert it into a date object. It then takes the date object, coverts it into the Julian value measured from December 12, 1899, and converts it back into a date object.

    Date testDate = null;
    int testDays = 0;
    
    try {
        String dateAsString = "02/28/2006";
        SimpleDateFormat sdf = new SimpleDateFormat("MM/dd/yyyy");
        testDate = sdf.parse(dateAsString);
        //At this point dateObject has no time. Add 12 hours to place it at 12:00:00
        testDate.setTime(testDate.getTime() + (12000*60*60));
        System.out.println(dateAsString + " -----> " + testDate);
    } catch (Exception e) {
    }
        
    //Conversion Pass 
    testDays = test.getDaysSince1900(testDate);
    System.out.println(testDate + " -----> " + testDays);
  
    testDate = test.getDateSince1900(testDays);
    testDate.setTime(testDate.getTime() + (12000*60*60));
    System.out.println(testDays + " -----> " + testDate); 

The results of the above test are shown here.

    02/28/2006 -----> Tue Feb 28 12:00:00 CST 2006
    
    Tue Feb 28 12:00:00 CST 2006 -----> 38775
    38775 -----> Tue Feb 28 12:00:00 CST 2006
    
    Tue Feb 28 12:00:00 CST 2006 -----> 38775
    38775 -----> Tue Feb 28 12:00:00 CST 2006