[Solved] Date to Text with concat or function
Posted: Wed Sep 25, 2019 6:35 pm
Hello
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 ?
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
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