Tuesday, August 4, 2009

Dealing with Dates: Sorting Legacy Month/Day/Year Dates

Half of my Grails applications use legacy AS400 databases whose tables frequently have dates stored in 6-bytes in MMDDYY format. I map those columns to Date attributes in my domain classes using a custom Hibernate type class (written in Groovy) and everything is hunky-dory -- until I try to sort or filter values on those dates.
The solution is actually quite simple: use HSQL with an algorithm to convert the dates to YYMMDD.

The following looks for contract prices where August 14, 2009 is between the MMDDYY begin and expire dates:


def query =
ContractPrice.executeQuery(
"""FROM ContractPrice cp WHERE
? BETWEEN
MOD((cp.beginDate * 10000.01), 1000000)
AND
MOD((cp.expireDate * 10000.01), 1000000)
""",
[90814],
[max:params.max.toInteger(),
offset:params.offset.toInteger(),
sort:params.sort])


The trick is an old RPG programmers trick of multiplying the MDY date by 10000.01.
Note that the query will be fairly slow as it will have to do a table scan. But what my applications normally do is use precedes the date math with a predicate on a column that performs well with the query optimizer.