cancatenate two dates with calculated field in appgini app

Got something cool to share with AppGini users? Feel free to post it here!
Post Reply
fgazza
Veteran Member
Posts: 205
Joined: 2019-04-30 17:37

cancatenate two dates with calculated field in appgini app

Post by fgazza » 2021-12-07 20:01

Hello to all.

I am trying to use the "calculated fields" feature of the appgini software to concatenate two date fields.

Since the dates are stored in the English format I would also like to display them in the European format.

My table is called macro_eventi

The two date fields are called data_inizio and data_fine

I entered this query but it doesn't work.

Code: Select all

SELECT CONCAT (DATE_FORMAT(`macro_eventi`.`data_inizio`,`%d/%m/%Y`), DATE_FORMAT(`macro_eventi`.`data_fine`,`%d/%m/%Y`)) FROM `macro_eventi` 
WHERE `macro_eventi`.`ma_ev_pri_key`='%ID%'
Can anyone help me by suggesting how to correctly write the query?

THANK YOU!!!
Fabiano

User avatar
jsetzer
AppGini Super Hero
AppGini Super Hero
Posts: 1807
Joined: 2018-07-06 06:03
Location: Kiel, Germany
Contact:

Re: cancatenate two dates with calculated field in appgini app

Post by jsetzer » 2021-12-07 21:23

CONCAT (DATE_FORMAT(`data_inizio`,`%d/%m/%Y`), DATE_FORMAT(`data_fine`,`%d/%m/%Y`))
(1) Please replace backticks around 2nd parameter of date_format function call by single qoutes, because the format is a string and not a table- or fieldname:

Code: Select all

DATE_FORMAT(`data_fine`,'%d/%m/%Y'))

(1) CONCAT will append strings and therefore will return a varchar result. If your field is varchar 20 for example, the SQL command you have given should return something like this:
01/01/202131/12/2012

(2) Is your field varchar and long enough (at least 20 characters)?

(3) I'm wondering if you might want a divider like this:

Code: Select all

01.01.2021 - 31.01.2021
If so, check out concat_ws function
Kind regards,
<js />

My AppGini Blog:
https://appgini.bizzworxx.de/blog

You can help us helping you:
Please always put code fragments inside [code]...[/code] blocks for better readability

AppGini 24.10 Revision 1579 + all AppGini Helper tools

fgazza
Veteran Member
Posts: 205
Joined: 2019-04-30 17:37

Re: cancatenate two dates with calculated field in appgini app

Post by fgazza » 2021-12-08 11:14

Thank you so much Jan.
you are a great expert and I always learn something from you !!!

I don't know if this has any limitations but in the end I solved in this way:

Code: Select all

SELECT CONCAT (DATE_FORMAT(`macro_eventi`.`data_inizio`,'%d/%m/%Y'),'-',DATE_FORMAT(`macro_eventi`.`data_fine`,'%d/%m/%Y')) FROM `macro_eventi` WHERE `macro_eventi`.`ma_ev_pri_key`='%ID%'
So I didn't use the concat_ws function because I was afraid of not being able to insert it correctly.
I have assumed this code but I am not sure if it works (i will test it in future).

Code: Select all

SELECT CONCAT_WS ('-',(DATE_FORMAT(`macro_eventi`.`data_inizio`,'%d/%m/%Y'),DATE_FORMAT(`macro_eventi`.`data_fine`,'%d/%m/%Y'))) FROM `macro_eventi` WHERE `macro_eventi`.`ma_ev_pri_key`='%ID%'
THANK YOU !!!

Post Reply