[Solved] Date to Text with concat or function

[gkick]

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 ?

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

[Villeroy]

Code: Select all

case 
  when datediff('yy', ...)>0 then datediff('yy', ...) || ' years'
  when datediff('mm', ...)>0 then datediff('mm', ...) || ' months'
else
    datediff('dd', ...) || ' days'
end AS "No contact since"

[chrisb]

i wrote the code below in order to show elapsed time (e.g. age) in years, months & days using functions contained in hsql 2.x.
the code will not work with the default embedded db.
two fields are used 'name' (varchar) & 'dob' (date).
i have included four rows of literal values to enable ease of testing.
it's easy to change the date values to those of your choice for testing purposes.
just paste into the query design window, activate the 'SQL' icon & hit execute.

Edit: 5 Nov 2019 code was faulty now fixed.

Code: Select all

--this code uses literal values
--to process your own data add your table name immediately under 'from' and delete everything below that
--replace all instances of 'dob' with the name of your date field
--replace all instances of 'name' with the name of your name field

select
	name, to_char(dob, 'DD MON YYYY') "Date",
	year(current_date) - year(dob)
	-
	case
	   when month(dob)>month(current_date) then 1
	   when month(dob)<month(current_date) then 0
	   when dayofmonth(dob)>dayofmonth(current_date) then 1
	   else 0
	end
	|| ' yr ' ||
	mod(floor(months_between(current_timestamp, timestamp(dob || ' 00:00:00'))),12)
	|| ' mon ' ||
	case
		when dayofmonth(current_date) >= dayofmonth(dob) then dayofmonth(current_date) - dayofmonth(dob)
		else
--		age calculators use 1 of 2 formulas to calculate the day value.
--		they may produce unequal results when day(birthdate) > day(current_date). both results are considered valid
--		i prefer formula 2 but you can use either
-----------------------------------------------------------------------
--		formula 1 = 
--			dayofmonth(last_day(dateadd('mm', -1, current_date)))
--			+
--			dayofmonth(current_date)
--			-
--			dayofmonth(dob)
-----------------------------------------------------------------------
--		formula 2 =
			dayofmonth(last_day(dob)) - dayofmonth(dob) + dayofmonth(current_date)
-----------------------------------------------------------------------
	end
	|| ' day'
	"Age"
from
--"YourTableName" goes here. delete everything below
(
   select name, cast(dob as date) dob
   from
   ( values
      ('Sam Snow','2015-09-24'),
      ('Harry Hail','2016-02-29'),
      ('Robbie Rain','2017-10-01'),
      ('Wally Wind','2019-01-26')
   ) a (name,dob)
)
order by dob;

[gkick]

Thank you very much gentlemen ! This is fantastic, really appreciate this.

Kind regards
Gerhard