AIS recently helped one of our larger clients convert a legacy TimeMatters database to Interwoven CMS.  While the overall schema present in TimeMatters is open to much debate from a design (and performance) perspective, one of the most perplexing aspects is TimeMatters very unique method of handling Date and Time records.

Using the email table as an example, there are two fields which contain the actual date and time information.  Reverse engineering the algorithms behind date time handling in TimeMatters in support of the conversion proved to be an interesting exercise in mental and SQL gymnastics.  In the end, we were able to reverse engineer and produce a usable function for converting the information.

Using T-SQL (2005), the following function will perform the necessary conversion:
NOTE: this is assuming the email table is being referenced in the query.

dateadd(ms, email.[time] * 10, cast(’12/28/1800′ as datetime) + email.[date])

(I don’t make this stuff up, I just share it to prevent hair pulling for others)