AppGini Community Forums

Posted: **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

Posted: **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

Posted: **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 !!!

AppGini Community Forums

cancatenate two dates with calculated field in appgini app

cancatenate two dates with calculated field in appgini app

Re: cancatenate two dates with calculated field in appgini app

Re: cancatenate two dates with calculated field in appgini app