DATE_FORMAT Function: Displaying Date and Time Using Formulas
You can use the DATE_FORMAT function in a formula to change the date and time format and the time zone. The DATE_FORMAT function is also used when concatenating date and time with other strings.
DATE_FORMAT(Formula, "Date and time format", "Time zone") |
Parameters of the DATE_FORMAT function is used in any of the following format:
Parameters in the Function Format
Field | Description |
---|---|
Formula |
For formula, the following fields can be used:
Specify a field with a field code. To add or subtract time to/from date and time, specify time in seconds in the formula. For example, specify "3600" or "60*60*1" for one hour. For one day, specify "24*60*60*1". If the result of the formula is in number, the number is treated as 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. If the date and time is 2016-4-1 AM 00:00 (UTC), the UNIX time is "1459468800". |
UNIX time | Specify UNIX time that is less than 10,000,000,000. |
Date and time format | Specify the date and time format of the converted value. For the format, see the following section: Date and Time Format |
Time Zone |
Specify the changed time zone ID or "system". When "system" is specified, the standard time zone that is set by the administrator is used. For details on the ID for each time zone, see the following page: Example) DATE_FORMAT(date-time, "YYYY/MM/dd HH:mm ZZ", "America/Los_Angeles") works as follows:
|
Date and Time Format
Specify the date and time format by using the format specifiers listed in the following chart. 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 Japanese: 4 Chinese: 四月 |
MM | Converts to a month with zero-padding. | 04 |
M | Converts to a month without zero-padding. | Apr |
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 |
Example Formula
This section explains example formulas that use the DATE_FORMAT format.
Formula: DATE_FORMAT(Field A, "YYYY-M-d", "Etc/GMT") Changes the format of Field A to YYYY-M-d. If Field A is "2020-04-01", the result is "2020-4-1". |
Formula: DATE_FORMAT(Field A, "YYYY-MM-dd HH:mm", "America/Los_Angeles") Changes the time zone of Field A to Pacific Standard Time. If Field A is "2020-04-01 13:00", the result is "2020-03-31 21:00". |
Formula: Field A & "-" & DATE_FORMAT(Created datetime, "YYYYMMdd", "system") Concatenates the string in Field A and the created date and time of the record. IF Field A is "Jennifer Anderson" and the created date and time of the record is "2020-04-01 13:00", the result is "Jennifer Anderson-20200401". |