- 06 Jul 2022
- 3 Minutes to read
- Print
- DarkLight
Supported format specifiers for DATETIME_FORMAT
- Updated on 06 Jul 2022
- 3 Minutes to read
- Print
- DarkLight
The DATETIME_FORMAT function will allow you to reformat the data from the date-type field into a string of your specifications. This is written in the format of DATETIME_FORMAT(Datetime, 'format specifier')
, in which the format specifier can be something like 'DD-MM-YYYY,' 'YYYY/MM/DD,' 'MM.DD,' etc. So for example, the formula DATETIME_FORMAT('10/22/2003', 'M')
will return 10
.
For more on how to use the DATETIME_FORMAT function in Airtable formulas, please see the article on Formulas and date fields. For more information on using DATETIME_FORMAT with timezones, please see the support article for Timezones and locales.
The following is a table of the supported format specifiers:
Format Specifier | Description | Example(s) |
---|---|---|
M | Month of the year, cardinal nos. 1-12 | 1 2 ... 11 12 |
Mo | Month of the year, ordinal nos. 1st-12th | 1st 2nd ... 11th 12th |
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 4 |
Qo | Quarter of the year, ordinal nos. 1st-4th | 1st 2nd 3rd 4th |
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 |
DDD | Day of the year, cardinal nos. 1-365 | 1 2 ... 364 365 |
DDDo | Day of the year, ordinal nos. 1st-365th | 1st 2nd ... 364th 365th |
DDDD | Day of the year, cardinal nos. 001-365 | 001 002 ... 364 365 |
d | Day of the week, cardinal nos. 0-6 | 0 1 ... 5 6 |
do | Day of the week, ordinal nos. 0th-6th | 0th 1st ... 5th 6th |
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 |
e | Day of the week (Locale) | 0 1 ... 5 6 |
E | Day of the week (ISO) | 1 2 ... 6 7 |
w | Week of the year, cardinal nos. 1-53 | 1 2 ... 52 53 |
wo | Week of the year, ordinal nos. 1st-53rd | 1st 2nd ... 52nd 53rd |
ww | Week of the year, cardinal nos. 01-53 | 01 02 ... 52 53 |
W | Week of the year (ISO), cardinal nos. 1-53 | 1 2 ... 52 53 |
Wo | Week of the year (ISO), ordinal nos. 1st-53rd | 1st 2nd ... 52nd 53rd |
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 |
gg | Week year, last two digits | 00 01 ... 98 99 |
gggg | Week year | 00 01 ... 98 99 |
GG | Week year (ISO), last two digits | 00 01 ... 98 99 |
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 |
S | Fractional second | 0 1 ... 8 9 |
SS | Fractional second | 00 01 ... 98 99 |
SSS | Fractional second | 000 001 ... 998 999 |
SSSS ... SSSSSSSSS | Fractional second | 000[0..] ... 999[0..] |
Z | Timezone relative to GMT, inc. colons | -07:00 -06:00 ... +06:00 +07:00 |
ZZ | Timezone relative to GMT, not inc. colons | -0700 -0600 ... +0600 +0700 |
X | Unix timestamp | 1360013296 |
x | Unix millisecond timestamp | 1360013296123 |
LT | Preset timestamp format in the form of h:mm A | 6:30 PM |
LTS | Preset timestamp format in the form of h:mm:ss A | 6:30:45 PM |
L | Preset date format in the form of MM/DD/YYYY | 06/08/2016 |
l | Preset date format in the form of M/D/YYYY | 6/8/2016 |
LL | Preset date format in the form of MMMM D, YYYY | June 8, 2016 |
ll | Preset date format in the form of MMM D, YYYY | Jun 8, 2016 |
LLL | Preset datetime format in the form of MMMM D, YYYY h:mm A | June 8, 2016 6:30 PM |
lll | Preset datetime format in the form of MMM D, YYYY h:mm A | Jun 8, 2016 6:30 PM |
LLLL | Preset datetime format in the form of dddd, MMMM D, YYYY h:mm A | Wednesday, June 8, 2016 6:30 PM |
llll | Preset datetime format in the form of ddd, MMM D, YYYY h:mm A | Wed, Jun 8, 2016 6:30 PM |
Clarifications
The format specifiers w
and W
behave slightly differently from one another. W
is the "ISO week of year" (see Wikipedia for more details). w
is for the "locale week of year" (in our case, according to the Gregorian calendar in GMT). 2/11/21 falls on the 6th ISO week and on the 7th Gregorian week. Behind the scenes, Airtable uses the moment library for date parsing. All of the tokens are documented here.