Converting Clarion TPS Date values to normal date fields

And now something totally wierd: if anyone has had the task to convert data from Clarion's TPS database files (anyone still remembers Clarion?), (s)he will have a problem of converting date fields. Clarion stores dates as long values in it's own format, which is not Unix Timestamp or anything similar - it's just Clarion. Well, after some time spent with a try-error-tryagain method, I found out that the "basic" date is 28.12.1800, and the long value is number of days that have passed after that, Clarion's TPS database does not have the datetime field, so you don't have to bother with time part.

There is a VBA function for converting these values in MS Access, but it would look more-less the same in any normal programming language.

Public Function GetClarionDate(ClDate)
If ClDate = 0 Then
GetClarionDate= Null
GetClarionDate = DateAdd("d", CLng(ClDate), CDate("12/28/1800"))
End If
End Function

This blog post has moved to a new address. If you would like to leave a comment, please do it there.

Published Wednesday, November 08, 2006 2:00 PM by Adis Jugo
Filed under: , ,