date
40 date types: 35 with a format string, 11 a sibling would misread, 18 standard in CLDR locales, 5 that decompose into parts, 1 in Meridian's open data.
The 40 date types
2024-03-04 05:06:07 in each type's format, against every sibling's rule
| Type | Format | Writes it as | Also accepted by |
|---|---|---|---|
Short Date (Day-Month-Year, 2-digit year) short_dmy · DATE | %d-%m-%y | 04-03-24 | short_mdy → 2024-04-03; short_ymd → 2004-03-24 |
Short Date (Month-Day-Year, 2-digit year) short_mdy · DATE | %m-%d-%y | 03-04-24 | short_dmy → 2024-04-03; short_ymd → 2003-04-24 |
Compact Date (DDMMYYYY) compact_dmy · DATE | %d%m%Y | 04032024 | compact_mdy → 2024-04-03 |
Compact Date (MMDDYYYY) compact_mdy · DATE | %m%d%Y | 03042024 | compact_dmy → 2024-04-03 |
DMY Date (dash-separated) dmy_dash · DATE | %d-%m-%Y | 04-03-2024 | mdy_dash → 2024-04-03 |
DMY Short Date (slash, 2-digit year) dmy_short_slash · DATE | %d/%m/%y | 04/03/24 | mdy_short_slash → 2024-04-03 |
DMY Date (slash-separated) dmy_slash · DATE | %d/%m/%Y | 04/03/2024 | mdy_slash → 2024-04-03 |
MDY Date (dash-separated) mdy_dash · DATE | %m-%d-%Y | 03-04-2024 | dmy_dash → 2024-04-03 |
MDY Short Date (slash, 2-digit year) mdy_short_slash · DATE | %m/%d/%y | 03/04/24 | dmy_short_slash → 2024-04-03 |
MDY Date (slash-separated) mdy_slash · DATE | %m/%d/%Y | 03/04/2024 | dmy_slash → 2024-04-03 |
Short Date (Year-Month-Day, 2-digit year) short_ymd · DATE | %y-%m-%d | 24-03-04 | short_dmy → 2004-03-24 |
Abbreviated Month Day Year (no comma) abbrev_month_no_comma · DATE | %b %d %Y | Mar 04 2024 | — |
Abbreviated Month Date abbreviated_month · DATE | %b %d, %Y | Mar 04, 2024 | — |
Chinese Standard Date chinese_ymd · DATE | — | 2024年1月15日sample | — |
Compact Year-Month (YYYYMM) compact_ym · DATE | %Y%m | 202403 | — |
Compact Date (YYYYMMDD) compact_ymd · DATE | %Y%m%d | 20240304 | — |
DMY Date with Abbreviated Month (dash-separated) dmy_dash_abbrev · DATE | %d-%b-%Y | 04-Mar-2024 | — |
DMY Date with Abbreviated Month (dash, 2-digit year) dmy_dash_abbrev_short · DATE | %d-%b-%y | 04-Mar-24 | — |
DMY Date (dot-separated) dmy_dot · DATE | %d.%m.%Y | 04.03.2024 | — |
DMY Short Date (dot, 2-digit year) dmy_short_dot · DATE | %d.%m.%y | 04.03.24 | — |
DMY Date with Abbreviated Month (space-separated) dmy_space_abbrev · DATE | %d %b %Y | 04 Mar 2024 | — |
DMY Date with Full Month (space-separated) dmy_space_full · DATE | %d %B %Y | 04 March 2024 | — |
Full Month Day Year (no comma) full_month_no_comma · DATE | %B %d %Y | March 04 2024 | — |
ISO Date iso · DATE | %Y-%m-%d | 2024-03-04 | — |
ISO Week Date iso_week · DATE | %G-W%V | 2024-W10 | — |
Japanese Imperial Year (Long/Kanji) jp_era_long · DATE | — | 令和6年1月15日sample | — |
Japanese Imperial Year (Short) jp_era_short · DATE | — | R6/01/15sample | — |
Julian Date julian · DATE | — | 24-015sample | — |
Korean Standard Date korean_ymd · DATE | — | 2024년 1월 15일sample | — |
Long Date with Full Month Name long_full_month · DATE | %B %d, %Y | March 04, 2024 | — |
Abbreviated Month Year month_year_abbrev · DATE | %b %Y | Mar 2024 | — |
Full Month Year month_year_full · DATE | %B %Y | March 2024 | — |
Month/Year (slash-separated) month_year_slash · DATE | %m/%Y | 03/2024 | — |
Ordinal Date ordinal · DATE | %Y-%j | 2024-064 | — |
Full Weekday with Abbreviated Month weekday_abbreviated_month · DATE | %A, %b %d, %Y | Monday, Mar 04, 2024 | — |
Weekday DMY Full (formal) weekday_dmy_full · DATE | %A, %d %B %Y | Monday, 04 March 2024 | — |
Full Weekday with Full Month Name weekday_full_month · DATE | %A, %B %d, %Y | Monday, March 04, 2024 | — |
Year-Month (ISO truncated) year_month · DATE | %Y-%m | 2024-03 | — |
YMD Date (dot-separated) ymd_dot · DATE | %Y.%m.%d | 2024.03.04 | — |
YMD Date (slash-separated) ymd_slash · DATE | %Y/%m/%d | 2024/03/04 | — |
Which date type is it?
3 of 40 date types accept it, and read it 3 ways.
| Type | Reads it as |
|---|---|
Short Date (Day-Month-Year, 2-digit year) short_dmy | 2024-03-04 |
Short Date (Month-Day-Year, 2-digit year) short_mdy | 2024-04-03 |
Short Date (Year-Month-Day, 2-digit year) short_ymd | 2004-03-24 |
Where each is the standard date
| Type | Length | Locales | Among them |
|---|---|---|---|
| Abbreviated Month Date | medium | 11 | en en-AS en-BI en-CA en-GU en-MH en-MP en-PH en-PR en-UM |
| DMY Date (dash-separated) | medium, short | 14 | brx en-MV es-CL ia jv mi nl nl-AW nl-BQ nl-CW |
| DMY Date (dot-separated) | medium, short | 32 | cv da da-GL de de-AT de-BE de-CH de-IT de-LI de-LU |
| DMY Short Date (dot, 2-digit year) | short | 30 | az az-Cyrl az-Latn cs de de-AT de-BE de-CH de-IT de-LI |
| DMY Short Date (slash, 2-digit year) | short | 35 | cy en-BE en-BW en-BZ en-IN es-MX fur gl id it |
| DMY Date (slash-separated) | medium, short | 196 | am asa bem bez br cgg dav ebu en-001 en-150 |
| DMY Date with Abbreviated Month (space-separated) | medium | 2 | en-AE naq |
| DMY Date with Full Month (space-separated) | long | 96 | en-001 en-150 en-AE en-AG en-AI en-AT en-AU en-BB en-BE en-BM |
| ISO Date | medium, short | 133 | aa aa-DJ aa-ER ab af af-NA an ann apc arn |
| Long Date with Full Month Name | long | 11 | en en-AS en-BI en-CA en-GU en-MH en-MP en-PH en-PR en-UM |
| MDY Short Date (slash, 2-digit year) | short | 2 | es-PA es-PR |
| MDY Date (slash-separated) | medium, short | 3 | es-PA es-PR iu |
| Short Date (Day-Month-Year, 2-digit year) | short | 4 | es-CL fy rm te |
| Short Date (Year-Month-Day, 2-digit year) | short | 1 | eo |
| Weekday DMY Full (formal) | full | 94 | en-001 en-150 en-AE en-AG en-AI en-AT en-AU en-BB en-BE en-BM |
| Full Weekday with Full Month Name | full | 11 | en en-AS en-BI en-CA en-GU en-MH en-MP en-PH en-PR en-UM |
| YMD Date (dot-separated) | medium, short | 2 | mn mn-Mong-MN |
| YMD Date (slash-separated) | short | 1 | en-ZA |
In Meridian's open data
- ISO Dategleif
.initial_registrationgleif.next_renewaledgar_gleif.as_of
How the engine tells them apart
- DMY Date (dash-separated) — Ambiguous with mdy_dash when day <= 12.
- DMY Short Date (slash, 2-digit year) — Ambiguous with mdy_short_slash — resolved by column-mode inference.
- DMY Date (slash-separated) — Ambiguous with mdy_slash when day <= 12 — column-mode inference resolves by checking if any value has first component > 12.
- MDY Date (dash-separated) — Ambiguous with dmy_dash — resolved by column-mode inference checking if any value has first component > 12.
- MDY Short Date (slash, 2-digit year) — Ambiguous with dmy_short_slash — resolved by column-mode inference.
- MDY Date (slash-separated) — Ambiguous with dmy_slash when day <= 12.
- Short Date (Day-Month-Year, 2-digit year) — Ambiguous with short_mdy in single-value mode (same regex pattern).
- Short Date (Month-Day-Year, 2-digit year) — Same regex as short_dmy — requires column-mode inference to disambiguate.
Use it
CLI · DuckDB
$ printf '%s\n' '04-03-24' '25-03-24' > column.txt
$ finetype infer -f column.txt --mode column
SELECT ft_profile(v) FROM (VALUES ('04-03-24'), ('25-03-24')) t(v);