## [Solved] Date to Text with concat or function

Creating and using forms

### [Solved] Date to Text with concat or function

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 (22.7 KiB) Viewed 1250 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

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

Villeroy
Volunteer

Posts: 28220
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

### Re: Date to Text with concat or function

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 fieldselect   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

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