Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

Table of Contents

...

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.

Image Modified4

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

Insert the following query into the "SQL SELECT Query" field.

...


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.
Code Block
.

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


Image Modified

Figure 3: SQL Query

Result

Image Modified

Figure 4: Proper date format