Parameters for DATETIME_FORMAT formula
DATETIME_FORMAT formula presents a datetime value in a specified manner. There are different parameters that can be used to present your DATETIME data field into a preferred format by modifying the highlighted part of the formula: datetime_format({arg},' specified output format'):
| Output format | Formula example | Output expected result | Output example | Transformations preview |
|---|---|---|---|---|
| M |
|
Month of the year, cardinal nos. 1-12 | 1 2 ... 11 12 |
|
| MM |
|
Month of the year, cardinal nos. 01-12 | 01 02 ... 11 12 |
|
| MMM |
|
Month name, three-letter abbreviation | Jan Feb ... Nov Dec |
|
| MMMM |
|
Month name | January ... December |
|
| Q |
|
Quarter of the year, cardinal nos. 1-4 | 1 2 3 |
|
| D |
|
Day of the month, cardinal nos. 1-31 | 1 2 ... 30 31 |
|
| Do |
|
Day of the month, ordinal nos. 1st-31st | 1st 2nd ... 30th 31st |
|
| DD |
|
Day of the month, cardinal nos. 01-31 | 01 02 ... 30 31 |
|
| d |
|
Day of the week, cardinal nos. 0-6 | 0 1 ... 5 6 |
|
| dd |
|
Day of the week, two-letter abbreviation | Su Mo ... Fr Sa |
|
| ddd |
|
Day of the week, three-letter abbreviation | Sun Mon ... Fri Sat |
|
| dddd |
|
Day of the week | Sunday ... Saturday |
|
| w |
|
Week of the year, cardinal nos. 1-53 | 1 2 ... 52 53 |
|
| ww |
|
Week of the year, cardinal nos. 01-53 | 01 02 ... 52 53 |
|
| WW |
|
Week of the year (ISO), cardinal nos. 01-53 | 01 02 ... 52 53 |
|
| YY |
|
Year, last two digits | 00 01 ... 98 99 |
|
| YYYY |
|
Year | 2000 2001 ... 2098 2099 |
|
| GGGG |
|
Week year (ISO) | 2000 2001 ... 2098 2099 |
|
| A |
|
Ante meridiem/post meridiem (majuscule) | AM PM |
|
| a |
|
Ante meridiem/post meridiem (miniscule) | am pm |
|
| H |
|
Hour, using a 24-hour clock from 0-23 | 0 1 ... 22 23 |
|
| HH |
|
Hour, using a 24-hour clock from 00-23 | 00 01 ... 22 23 |
|
| h |
|
Hour, using a 12-hour clock from 1-12 | 1 2 ... 11 12 |
|
| hh |
|
Hour, using a 12-hour clock from 01-12 | 01 02 ... 11 12 |
|
| m |
|
Minute | 0 1 ... 58 59 |
|
| mm |
|
Minute | 00 01 ... 58 59 |
|
| s |
|
Second | 0 1 ... 58 59 |
|
| ss |
|
Second | 00 01 ... 58 59 |
|
| SSS |
|
Fractional second | 000 001 ... 998 999 |
|
| Z |
|
Time zone relative to GMT, inc. colons | -07:00 -06:00 ... +06:00 +07:00 |
|
| ZZ |
|
Time zone relative to GMT, not inc. colons | -0700 -0600 ... +0600 +0700 |
|
| X |
|
Unix timestamp | 1360013296 |
|
Notes:
- The formula supports various combinations of format specifiers:
- For example, 1991-08-24T15:12:52.567Z value of Datetime-formatted cell can be used in a following formula DATETIME_FORMAT({date},'Do of MMMM, YYYY') to generate a result of "24th of August, 1991".