Friday, July 18, 2008

How to Convert Julian Dates to Gregorian

I'm writing Java / Jython / Groovy these days, not F#, at a new job, and haven't had time to maintain this blog, but I did want to post one little snippet that I was surprised not to find elsewhere. I've had to ingest 10s of 1000s of records from a customer's Microsoft Access database, and the dates are represented in Julian format. Not relative to the Julian calendar; I mean the number of days since a particular Day 0. For Microsoft Access, Day 0 is December 30, 1899. If Microsoft Access interests you, read up on it here.

Anyway, there are quite a few Web pages out there describing how to do it in Excel or T-SQL, but none describing how to do it in Java. Actually, it's ridiculously easy, which doesn't say much for me. Here's the solution in Jython:

from java.util import *

def convertJulian(julian):
calendar = Calendar.getInstance()
# Why in God's name are months in Java 0-based?
# This is December 30, 1899.
calendar.set(1899, 11, 30)
calendar.add(Calendar.DATE, julian)
return calendar.time

D'oh! Set Day 0, add the days, and you're good to go. Now in Java:

private static Date convertJulian(final int julian) {
Calendar calendar = Calendar.getInstance();
calendar.set(1899, 11, 30);
calendar.add(Calendar.DATE, julian);
return calendar.getTime();
}

Finally, as XSLT (you'll need an XSLT 2.0-compliant processor for this):

<xsl:function name="local:convert-access-date" as="xs:date">
<xsl:param name="access-date" as="xs:integer"/>
<xsl:variable name="days-since-zero" as="xs:dayTimeDuration"
select="xs:dayTimeDuration(concat('P', $access-date, 'D'))"/>
<xsl:sequence select="$zero + $days-since-zero"/>
</xsl:function>