DATE_FORMAT Function: Displaying Date and Time by Specifying Its Display Format

You can use DATE_FORMAT function to display a date and time value in the specified display format.
DATE_FORMAT function converts the date and time value into a string in the specified date and time format, and then displays it to you. The DATE_FORMAT function is also used when concatenating date and time with other strings.

Basic Usage of DATE_FORMAT Function

DATE_FORMAT(Field_Code, "Date and time format", "Time Zone")

For detailed examples, refer to Formula Examples.

Details on Arguments

In DATE_FORMAT function, you need to specify the first argument (Field code), the second argument (Date and time format), and the third argument (Time zone). The following sections describe the basic usage of each argument.

The First Argument: Field Code

You can use the following fields as the first argument. Specify a field with a field code.

  • Date and time
    You can specify the date and time between January 1, 1000 00:00 (UTC) and December 31, 9999 23:59 (UTC).
  • Date
  • Time
  • Created datetime
  • Updated datetime
  • Number
  • Calculated

You can also use the following operators and functions.
+, -, *, /, ^, SUM

Not only the date and time you enter in the field, but you can also specify the calculated date, such as adding or subtracting hours from the field used for date and time. In formulas, specify a time in seconds. For example, specify "3600" or "60*60*1" to represent an hour.

  • Example of displaying the date of the following day:
    DATE_FORMAT(Date+24*60*60*1, "MM/dd/YYYY", "Etc/GMT")

The Second Argument: Date and Time Format

As the second argument, you need to specify the date and time using the format specifiers in the following table. Each format specifier is converted to the corresponding string.

Format Specifier Description String when 2020-4-1 PM 00:00 is converted
YYYY or yyyy Converts to a western calendar year. 2020
MMMM Converts to a month corresponding to the display language that is set by the user who edited the record. English: April
Japanese: 4月
Chinese: 四月
MMM Converts to a month in abbreviation corresponding to the display language that is set by the user who edited the record. English: Apr
MM Converts to a month with zero-padding. 04
M Converts to a month without zero-padding. 4
dd Converts to a day with zero-padding. 01
d Converts to a day without zero-padding. 1
a Converts to "AM" or "PM" corresponding to the display language that is set by the user who edited the record. English: PM
Japanese: 午後
Chinese: 下午
KK Converts to the 12-hour clock display where 0 to 11 o'clock is considered as being before noon. Zero-padding is applied. 00
K Converts to the 12-hour clock display where 0 to 11 o'clock is considered as being before noon. Zero-padding is not applied. 0
hh Converts to the 12-hour clock display where 1 to 12 o'clock is considered as being before noon. Zero-padding is applied. 12
h Converts to the 12-hour clock display where 1 to 12 o'clock is considered as being before noon. Zero-padding is not applied. 12
HH Converts to the 24-hour clock display where one day refers to the time between 0 to 23 o'clock. Zero-padding is applied. 12
H Converts to the 24-hour clock display where one day refers to the time between 0 to 23 o'clock. Zero-padding is not applied. 12
kk Converts to the 24-hour clock display where one day refers to the time between 1 to 24 o'clock. Zero-padding is applied. 12
k Converts to the 24-hour clock display where one day refers to the time between 1 to 24 o'clock. Zero-padding is not applied. 12
mm Converts to the minute display with zero-padding. 00
m Converts to the minute display without zero-padding. 0
ss Converts to the second display with zero-padding. 00
s Converts to the second display without zero-padding. 0
Z Converts to the time difference from UTC (Coordinated Universal Time). The format is "hhmm". +0900
ZZ Converts to the time difference from UTC (Coordinated Universal Time). The format is "hh:mm". +09:00
ZZZ Converts to the time zone ID. Asia/Tokyo

The Third Argument: Time Zone

As the third argument, you need to specify a time zone.
If you have specified the Date or Time field as the first argument, specify "Etc/GMT" (Time zone ID of Coordinated Universal Time).
If you have specified other fields, you can choose which time zone to use for display.
Example: DATE_FORMAT(Date_and_Time, "M-d-YYYY", "Asia/Tokyo")

When you specify the time zone that has daylight savings time, daylight savings time is applied to the calculated result.
Example) DATE_FORMAT(Date_and_Time, "MM/dd/YYYY HH:mm ZZ", "America/Los_Angeles")

  • 2019-01-01 00:00 in UTC is converted to 12/31/2018 16:00 - 08:00.
  • 2018-08-01 00:00 in UTC is converted to 07/31/2018 17:00 - 07:00.

If you specify "system", the time zone selected in Kintone Administration will be applied.

Formula Examples

This section explains formula examples that use the DATE_FORMAT format.
As the calculated result of the DATE_FORMAT function is a string, you need to use the Text field to write a formula.

Formula: DATE_FORMAT(Date_and_Time, "M-d-YYYY", "Etc/GMT")

Changes the format of the "Date and time" field to M-d-YYYY.
If the value of the "Date and time" field is "04-01-2020", the calculated result is 4-1-2020.
Formula: DATE_FORMAT(Date_and_Time, "MM-dd-YYYY HH:mm", "America/Los_Angeles")

Changes the format of "Date and time" field to "MM-dd-YYYY HH:mm". Changes the time zone to Pacific Standard Time.
If the value of the "Date and time" field is "04-01-2020 13:00", the calculated result is 03-31-2020 21:00 (conversion from JST).
Formula: Name & "-" & DATE_FORMAT(Created_Datetime, "MMddYYYY", "system")

Concatenates the string in "Name" field with the Created time of the record.
If the "Name" field is "Maria Jackson" and the created datetime of the record is "06-21-2021 13:00", the calculated result is "Maria Jackson-06212021".

Refer to "Displaying Data By Concatenating Date with String"
Formula: DATE_FORMAT(Date_of_Birth, "MM", "Etc/GMT")

Calculates the date of birth using the value in the "Date of Birth" field.
If the value of the "Date of Birth" field is "12-31-1969", the calculated result is "12".
Formula: DATE_FORMAT(Application_Date + (60*60*24*30), "MM-dd-YYYY", "Etc/GMT")

Calculates the end of the trial period that is 30 days after the value in the "Application Date" field.
If the value of the "Application Date" field is "07-01-2021", the calculated result is "07-31-2021".

TIPS: UNIX time

UNIX time is the number of seconds elapsed since 1970-1-1 AM 00:00 UTC. It is also called UNIX time stamp. For example, if the date and time is 4-1-2016 AM 00:00 (UTC), the UNIX time is "1459468800".
You can also specify a UNIX time as the first argument. For UNIX time, you can specify a value between -30610224000 and 253402300799.
If you have specified the number field or the calculated field as the first argument, the values in each field will be treated as UNIX time.