Introduction

At times, when utilizing aggregate queries on dates, such as Max(), Min(), and AVG(), the resulting columns will yield the following:

Figure 1: Inappropriate date format

Steps Example

To solve the issue, instead of using "Advanced Form Data' as the Data Store. We can use "Database SQL Query" instead.

4

Figure 2: Select the source of data as Database SQL Query


This is an example. 
To implement this into your list: 
1. Replace "app_fd_tablename" with your desired table name.
2. Replace dataCreated / dateModified with the desired date column.
3. Replace the aliases with custom aliases.
4. You may configure the date format (%Y-%m-%d %H:%i:%s) accordingly.
5. Insert the following query into the "SQL SELECT Query" field.
SELECT 
    DATE_FORMAT(MAX(dateCreated), '%Y-%m-%d %H:%i:%s') AS max_dateCreated, 
    DATE_FORMAT(MIN(dateModified), '%Y-%m-%d %H:%i:%s') AS min_dateModified
FROM 
    app_fd_tablename


Figure 3: SQL Query

Result

Figure 4: Proper date format


  • No labels