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 =
"""FROM ContractPrice cp WHERE
MOD((cp.beginDate * 10000.01), 1000000)
MOD((cp.expireDate * 10000.01), 1000000)

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.

1 comment:

Aaron Bartell said...

chant with me "we want more posts, we want more posts" :-) Nothing since Aug 2009? Don, where are you?