DATE_FORMAT Function: Specifying the Display Format of Date and Time
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
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
If you want to specify a field code, you can use a field code of one 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.
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.
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.
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.
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.
DATE_FORMAT(date_and_time, "M-d-YYYY", "Asia/Tokyo")If you specify "system", the time zone selected in cybozu.cn 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.
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".
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. When noon is specified for the first argument ("date_and_time"), the time is converted to "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. When noon is specified for the first argument ("date_and_time"), the time is converted to "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. When noon is specified for the first argument ("date_and_time"), the time is converted to "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. When noon is specified for the first argument ("date_and_time"), the time is converted to "12". |
| HH | 00 | Converts to the 24-hour clock display where one day is from 0 to 23 o'clock. Zero-padding is applied. When midnight is specified for the first argument ("date_and_time"), the time is converted to "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. When midnight is specified for the first argument ("date_and_time"), the time is converted to "0". |
| kk | 24 | Converts to the 24-hour clock display where one day is from 1 to 24 o'clock. Zero-padding is applied. When midnight is specified for the first argument ("date_and_time"), the time is converted to "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. When midnight is specified for the first argument ("date_and_time"), the time is converted to "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".
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.
DATE_FORMAT(Date_and_time, "MM-dd-YYYY HH:mm", "America/Los_Angeles")If "2020-04-01 13:00" (JST) is entered in the "Date and time" field, 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.
Full_name & "-" & DATE_FORMAT(Created_datetime, "MMddYYYY", "system")
If "Maria Jackson" is entered in the "Full name" field and the record's created datetime is "Oct 31, 2022 4:42 PM", the calculation result will be "Maria Jackson-10312022".
Formula to display the month from a date of birth
The following formula displays the month from a date of birth.
DATE_FORMAT(Date_of_birth, "MM", "Etc/GMT")
If "Dec 31, 1969" is entered in the "Date of birth" field, "12" will be 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.
DATE_FORMAT(Application_date + (60*60*24*30), "MM-dd-YYYY", "Etc/GMT")
If "Jul 01, 2021" is entered in the "Application date" field, the calculation result will be "07-31-2021" (the date 30 days after the application date).