[Solved] Date to Text with concat or function

Creating and using forms

[Solved] Date to Text with concat or function

Postby gkick » 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 ?

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
Attachments
FollowUp.JPG
FollowUp.JPG (22.7 KiB) Viewed 1251 times
Last edited by gkick on Wed Sep 25, 2019 10:40 pm, edited 1 time in total.
Libre Office 6.2 on Windows 10 HSQL 2.5 backend
gkick
 
Posts: 221
Joined: Wed Aug 07, 2019 5:24 pm
Location: Chile

Re: Date to Text with concat or function

Postby Villeroy » Wed Sep 25, 2019 7:19 pm

Code: Select all   Expand viewCollapse view
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"
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 28220
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Date to Text with concat or function

Postby chrisb » Wed Sep 25, 2019 9:50 pm

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   Expand viewCollapse view
--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;
Last edited by chrisb on Tue Nov 05, 2019 3:39 am, edited 2 times in total.
open office 4.1.7 & LibreOffice 6.3.5.2 using HSQL 1.8.0.10 (Embedded) and HSQL 2.5.0 (Split) on Windows 10
chrisb
 
Posts: 219
Joined: Mon Jun 07, 2010 4:16 pm

Re: Date to Text with concat or function

Postby gkick » Wed Sep 25, 2019 10:04 pm

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

Kind regards
Gerhard
Libre Office 6.2 on Windows 10 HSQL 2.5 backend
gkick
 
Posts: 221
Joined: Wed Aug 07, 2019 5:24 pm
Location: Chile


Return to Forms

Who is online

Users browsing this forum: No registered users and 1 guest