I have this form which tells me how long there was no sign of life from a contact. The current calculation is a standard datediff finding the difference between the last contact field and the current date. As such I can display either number of days or number of months or number of years.

However as the values can be large something like 231 month is pretty meaningless, as is a null year if the period is only 15 days.

Studied the HSQL man for functions, but not sure if a function exists which will take the datediff integer and convert it into something like

29 days or

1 month and 2 days or

3 years 4 months 12 days

Guess one way is to take the integer (which is days) and divide it by 12

if the result is greater then 1 ignore and divide by 365.35 and so forth and then use concatenation to put the strings together like 3 months and 4 days etc.

I wonder if there is a less painful solution ?

- Code: Select all Expand viewCollapse view
`SELECT "ReM" "Follow Up", "FirstName" + SPACE( 1 ) + "LastName" "With", CEILING( DATEDIFF( 'yy', CURRENT_DATE, CAST( YEAR( "LastContact" ) || '-' || MONTH( "LastContact" ) || '-' || DAY( "LastContact" ) AS "DATE" ) ) ) * - 1 "No contact since" FROM "tblContacts" WHERE WEEK( "ReM" ) >= WEEK( CURDATE( ) ) AND "FollowUp" = TRUE`