DATE_FORMAT Function: Specifying the Display Format of Date and Time

Article Number:040501

By using the DATE_FORMAT function in a formula, you can convert date, time, or date and time values into string values that use a specified date and time format.
The DATE_FORMAT function is also used when concatenating dates and times with other character strings.

DATE_FORMAT function syntax

The content has been copied.
DATE_FORMAT(date_and_time, "date_and_time_format","time_zone")

There are three arguments that you need to specify for the DATE_FORMAT function: "date_and_time", "date_and_time_format", and "time_zone", in that order.

The first argument: "date_and_time"

For the first argument, specify the date, time, or date and time for which you want to specify a display format.
You can specify the first argument using a field code, Unix time, or formula.

Specifying the first argument using a field code

You can specify a field code of the following fields.

  • "Date and time" fields
  • "Date" fields
  • "Time" fields
  • "Created datetime" fields
  • "Updated datetime" fields
  • "Number" fields
  • "Calculated" fields

If you specify the field code of a "Number" or "Calculated" field, the field's value will be treated as Unix time.

Specifying the first argument using a Unix time

Unix times can be specified directly.
You can specify a Unix time from -30610224000 to 253402300799.

Specifying the first argument using a formula

For the first argument, you can also specify a formula that results in a date, time, or date and time value. For example, you can specify a formula that produces a calculation result after adding or subtracting time from a date and time value.

The following operators and functions can be used when specifying formulas.

  • +
  • -
  • *
  • /
  • ^
  • SUM function

When entering a time in a formula, specify the time in seconds.
For example, specify "3600" or "60*60*1" to represent an hour.

The following example shows a formula that displays the next day's date.

The content has been copied.
DATE_FORMAT(Date+24*60*60*1, "MM/dd/YYYY", "Etc/GMT")

The second argument: "date_and_time_format"

For the second argument, specify a date and time format.
The date and time format can be specified in one of the following ways.

  • Specify a date and time format directly
  • Specify a field code

For information on how to specify a date and time format directly, refer to Date and time format codes.

If you want to specify a field code, you can use a field code of one of the following fields.

  • "Text" fields
  • "Text area" fields

If you want to add character strings to a date and time format

Enclose each of the character strings that you want to add in single quotation marks (i.e., '[character string]').
For example, if you want to display a calculation result in the format "Date: ***", specify the following.

The content has been copied.
DATE_FORMAT(field_code, "'Date:' d", "Asia/Tokyo")

If you want to add a single quotation mark (') as a character in a date and time format, specify two single quotation marks consecutively (i.e., '') in the date and time format.
For example, if you want to display a calculation result in the format " 'Date: ***' ", specify the following.

The content has been copied.
DATE_FORMAT(field_code, "'''Date:' d''", "Asia/Tokyo")

The third argument: "time_zone"

For the third argument, specify a time zone.

If you have specified a "Date" or "Time" field for the first argument, specify the time zone as "Etc/GMT" (the time zone ID for Coordinated Universal Time). If you specify a time zone other than "Etc/GMT", it may result in time or date deviations.

The content has been copied.
DATE_FORMAT(time, "hh:mm", "Etc/GMT")

If you have specified any other field type or Unix time for the first argument, specify the time zone of your choice.

The content has been copied.
DATE_FORMAT(date_and_time, "M-d-YYYY", "Asia/Tokyo")

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

Treatment of daylight saving time

If you specify a time zone that has daylight saving time, daylight saving time is applied to the calculation result.

The following example shows a formula that specifies a time zone that has daylight saving time.

The content has been copied.
DATE_FORMAT(date_and_time, "MM/dd/YYYY HH:mm ZZ", "America/Los_Angeles")

This formula displays the following calculation results.

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

Fields that can use the DATE_FORMAT function

To display the results of a calculation using a DATE_FORMAT function, use the function with a "Text" field.
Go to the settings screen of the relevant "Text" field, select the Calculate automatically checkbox, and enter a formula.

If you try to directly display results using the DATE_FORMAT function in a "Calculated" field, an error will occur.
However, you can use the DATE_FORMAT function with a "Calculated" field by placing it inside another function (e.g., as a conditional expression in an IF function).

Date and time format codes

The following format codes can be used in the date and time format specified as the second argument of a DATE_FORMAT function.

Format code for displaying years

Code Conversion example Description
YYYY 2020 Converts to a western calendar year. Lowercase "yyyy" can also be used.

Format codes for displaying months

Code Conversion example Description
MMMM April
4月
Converts to a month in the display language of the user who edits the record.
MMM Apr
4月
Converts to a month in the display language of the user who edits the record. English months are abbreviated.
MM 04 Converts to a month with zero-padding.
M 4 Converts to a month without zero-padding.

Format codes for displaying days

Code Conversion example Description
dd 01 Converts to a day with zero-padding.
d 1 Converts to a day without zero-padding.

Format codes for displaying times

Code Conversion example Description
a PM
午後
下午
Converts to the "AM" or "PM" marker in the display language of the user who edits the record.
KK 00 Converts to the 12-hour clock display where 0 to 11 o'clock is considered as being before noon. Zero-padding is applied.
Noon is converted as "00".
K 0 Converts to the 12-hour clock display where 0 to 11 o'clock is considered as being before noon. Zero-padding is not applied.
Noon is converted as "0".
hh 12 Converts to the 12-hour clock display where 1 to 12 o'clock is considered as being before noon. Zero-padding is applied.
Noon is converted as "12".
h 12 Converts to the 12-hour clock display where 1 to 12 o'clock is considered as being before noon. Zero-padding is not applied.
Noon is converted as "12".
HH 00 Converts to the 24-hour clock display where one day is from 0 to 23 o'clock. Zero-padding is applied.
Midnight is converted as "00".
H 0 Converts to the 24-hour clock display where one day is from 0 to 23 o'clock. Zero-padding is not applied.
Midnight is converted as "0".
kk 24 Converts to the 24-hour clock display where one day is from 1 to 24 o'clock. Zero-padding is applied.
Midnight is converted as "24".
k 24 Converts to the 24-hour clock display where one day is from 1 to 24 o'clock. Zero-padding is not applied.
Midnight is converted as "24".

Format codes for displaying minutes

Code Conversion example Description
mm 05 Converts to minutes with zero-padding.
m 5 Converts to minutes without zero-padding.

Format codes for displaying seconds

Seconds are not saved in kintone fields that handle dates and times.
If a field code is specified as the first argument of a DATE_FORMAT function, the number of seconds is treated as 0.

Code Conversion example Description
ss 01 Converts to seconds with zero-padding.
If you specify the Unix time "1695179101" (September 20, 2023 12:05:01 PM) with "ss" as the format, it will be converted to "01".
s 1 Converts to seconds without zero-padding.
If you specify the Unix time "1695179101" (September 20, 2023 PM 12:05:01) with "ss" as the format, it will be converted to "1".

Format codes for displaying time zones

Code Conversion example Description
Z +0900 Converts to the time difference from UTC (Coordinated Universal Time) based on the time zone specified as the third argument. The time difference is displayed in "hhmm" format.
ZZ +09:00 Converts to the time difference from UTC (Coordinated Universal Time) based on the time zone specified as the third argument. The time difference is displayed in "hh:mm" format.
ZZZ Asia/Tokyo Converts to the time zone ID of the time zone specified as the third argument.

Formula examples

When specifying a field in a formula, make sure to specify the field code as the argument (and not the field name).
In the examples below, the field code is set for each field as the field name with underscores instead of spaces (e.g., the field code for the "Application date" field is "Application_date").

Formula to change the format of a "Date" field to "M-d-YYYY"

The following formula changes the format of a "Date" field to "M-d-YYYY".

The content has been copied.
DATE_FORMAT(Date, "M-d-YYYY", "Etc/GMT")

If "2020-04-01" is entered in the "Date" field, it will be converted to "4-1-2020".

Formula to change the format and time zone of a "Date and time" field

The following formula changes a "Date and time" field's format to "MM-dd-YYYY HH:mm" and its time zone to Pacific Standard Time.

The content has been copied.
DATE_FORMAT(Date_and_time, "MM-dd-YYYY HH:mm", "America/Los_Angeles")

If the value of the "Date and time" field is entered as "2020-04-01 13:00" (JST), it will be converted to "03-31-2020 21:00".

Formula to display a name combined with a record's created datetime

The following formula displays the value entered in the "Full name" field combined with the date and time the record was created. The display format is "[full name]-[created datetime]".
The name, hyphen, and created datetime are combined using the "&" symbol.

The content has been copied.
Full_name & "-" & DATE_FORMAT(Created_datetime, "MMddYYYY", "system")

Screenshot: The value of the "Full name" field and the record's created datetime are combined

If the "Full name" field's value is entered as "Maria Jackson" and the record's created datetime is "Oct 31, 2022 4:42 PM", the calculation result is "Maria Jackson-10312022".

Formula to display the month from a date of birth

The following formula displays the month from a date of birth.

The content has been copied.
DATE_FORMAT(Date_of_birth, "MM", "Etc/GMT")

Screenshot: Displaying the month from the "Date of birth" field

If the value of the "Date of birth" field is entered as "Dec 31, 1969", "12" is displayed.

Formula to calculate a trial period end date that is 30 days after the application date

The following formula displays a trial period end date that is 30 days after the application date.

The content has been copied.
DATE_FORMAT(Application_date + (60*60*24*30), "MM-dd-YYYY", "Etc/GMT")

Screenshot: A trial period end date is automatically calculated

If the value of the "Application date" field is entered as "Jul 01, 2021", the calculation result is "07-31-2021" (the date 30 days after the application date).