DATE_FORMAT Function: Usage Example

By using the DATE_FORMAT function in a formula, date, date & time, and time can be treated as strings.
If you specify a field code ""Report creation date" & date" without using DATE_FORMAT function, result value is "Report creation date 1575504000", which cannot be read as a date.
When the date and time format is not specified, the date and time is displayed in numeric values called "UNIX time".

If you use the DATE_FORMAT function and specify ""Report creation date" & DATE_FORMAT(Date, "MM-dd", "Etc/GMT")", the date is displayed as "Report creation date 12-05".

Example 1: Concatenating Date and String

This section describes the way to concatenate the DATE_FORMAT function with a string, by using an example of the Operating Report app.
Image of the completed app:

  1. Click the app setting button Setting button on the upper right of the record view, and select the "Form" tab.

  2. Select the Date field and the Created by field in the parts list shown in the left side, and place them in the form.

  3. Place the Text field for displaying a title.
    Make the field width longer so that title is displayed in one line.

  4. Open the screen to set the Text field, and change the field name to "Title".
    Keep the setting screen open.

  5. Select "Calculate automatically", and enter the formula.

    • Formula:
      "Report creation date" & " " & DATE_FORMAT(Date, "MM-dd", "Etc/GMT") & " " & Created by
  6. Click Save on the field settings screen.

  7. Click Save Form on the upper left side of the screen, and then click Update App on the upper right side of the screen.

When you create a record in the Operating Report app, the report creation date you specified using the DATE_FORMAT function is displayed with the values of "Date" and "Created by".

If you set the Title field as a title of a record, it will be displayed as a header title of a notification. You can also display it in the list of records. See "Setting the Record Title".
See "Configuring Views".

This is also useful when you want to sort records by dates or by user names (in a "Task Management" app, etc.).

Example 2: Using the DATE_FORMAT Function within the IF Function

You can specify the DATE_FORMAT function within the IF function.

Formula Example
IF(Display_Method="Japanese Style", DATE_FORMAT(Date, "yyyy/MM/dd", "Etc/GMT"), IF(Display format="American Style", DATE_FORMAT(Date, "MM/dd/yyyy", "Etc/GMT"), DATE_FORMAT(Date, "dd/MM/yyyy", "Etc/GMT")))

The date will automatically be displayed in Japanese format if you select "Japanese Style", US format if you select "American Style", or British format if you select "British Style".

For details on the IF function, see the "IF Function: Changing the Value to Display Depending on the Condition" page.

This section describes the way to display dates for each countries, using the combination of the IF function and the DATE_FORMAT function in a minutes management app.

Image of the completed app:

  1. Click the app setting button Setting button on the upper right of the record view, and select the "Form" tab.

  2. From the parts list shown in the left side, drag and drop the Date field.
    Leave the default field code (Date) as is.

  3. Select the Text field and the Radio Button field in the parts list, and place them in the form.

  4. Open the screen to set the Radio Button field, and then change the field name to "Display method"and the field code to "Display_method".
    Set "Japanese Style", "American Style", and "British Style" as options.

  5. Click Save on the screen to set a radio button.

  6. Open the screen to set the Text field, and enter "date" as the field name.

  7. Select "Calculate automatically", and enter the formula.

    • Formula to change the date display according to the country selected
      IF(Display_Method="Japanese Style", DATE_FORMAT(Date, "yyyy/MM/dd", "Etc/GMT"), IF(Display format="American Style", DATE_FORMAT(Date, "MM/dd/yyyy", "Etc/GMT"), DATE_FORMAT(Date, "dd/MM/yyyy", "Etc/GMT")))
  8. Click Save on the screen to set the Text field.

  9. Click Save Form on the upper left side of the screen, and then click Update App on the upper right side of the screen.

Now, the date is displayed using the date format selected.

TIPS: If Date or Time of Calculated Result Is Off

If the date and/or the time in the calculated result of DATE_FORMAT function is different from what they should look, it is possible that the time zone is not set properly.
The time zone of the field for date and time is fixed at Coordinated Universal Time. To use these fields in formulas, specify the time zone ID "Etc/GMT" of Coordinated Universal Time. When you specify other time zones, you will not obtain the result as you expect.

For example, if you set "Osaka, Sapporo, Tokyo (Asia/Tokyo)" for a time zone, the time shown would be +9 hours.

  • Time Zone Calculation - Wrong:
    DATE_FORMAT(Field A, "HH:mm", "Asia/Tokyo")
    If the value in Field A is "9:00", the calculated result is: 18:00

By specifying UTC Time Zone ID "Etc/GMT", the correct time will be displayed.

  • Time Zone Calculation - Correct
    DATE_FORMAT(Field A, "HH:mm", "Etc/GMT")
    If the value in Field A is "9:00", the calculated result is: 09:00