MeridianMERIDIAN

DMY Date (dot-separated)

datetime.date.dmy_dot

Day.Month.Year with dot separators. Common in Germany, Austria, Switzerland, and Eastern European countries.

Worked samples

DuckDB 1.5.5 · finetype 0.6.64

ValueAs DATE
15.01.20242024-01-15
31.12.20192019-12-31
%d
day of month, two digits
%m
month, two digits
%Y
four-digit year

Written by

CLDR 46 via finetype 0.6.64

Each locale below writes this format with its CLDR date pattern at the styles named beside it.

cv short · da short · da (GL) short · de medium · de (AT, BE, CH, IT, LI, LU) medium · en (CH) short · fo medium · fo (DK) medium · gsw medium · gsw (FR, LI) medium · ku short · nb short · nb (SJ) short · nn short · no short · ro short · ro (MD) short · ru short · ru (BY, KG, KZ, MD, UA) short · se (FI) short · tk short · tt short

Shape

Pattern
^\d{2}\.\d{2}\.\d{4}$
ValueLengthShape
15.01.202410matches
31.12.201910matches
15.01.2029rejected: one character short

Background

Origin

Wikipedia describes the traditional all-numeric form of writing Gregorian dates in German as the little-endian day.month.year order, with a dot on the line as the separator.[1] Under the traditional German convention, years could be written with two or four digits.[1] Wikipedia explains that the dot as a separator matches the German convention of pronouncing the day and the month as ordinal numbers, which German writes followed by a dot.[1]

In 1995 the DIN 5008 standard, which defines common typographic conventions, replaced the traditional German date notation with the ISO 8601 notation.[1] Wikipedia says the ISO 8601 form has been becoming the prescribed date format in Germany since 1996-05-01.[1] Because portions of the population continued to use the old format, the traditional notation was re-introduced as an alternative in DIN 5008 in 2001.[1] DIN ISO 8601 re-introduced the traditional format as an alternative in September 2006, with its usage restricted to contexts where misinterpretation cannot occur.[1]

Wikipedia says that in most post-Soviet states the DD.MM.YYYY format is used with dots as separators and with leading zeros.[1] Wikipedia also notes that official EU documents still tend to use DD.MM.YYYY.[1]

Use

Wikipedia notes that the expanded German form of the date, such as 31. Dezember 1991, keeps the little-endian order and the ordinal-number dot after the day.[1] A column that mixes the all-numeric form with written-out month names needs more than one pattern, so profile it before you pick one. (Meridian's view)

DuckDB's strptime function converts strings to timestamps according to a specified pattern.[2] In DuckDB's format specifiers, %d is the day of the month as a zero-padded decimal.[2] In DuckDB's format specifiers, %m is the month as a zero-padded decimal number.[2] DuckDB's strptime function throws an error on failure.[2] DuckDB provides try_strptime to return NULL on failure instead.[2] Before you settle on %d.%m.%Y, scan the column for unpadded days and months and for two-digit years. (Meridian's view) Parsing with the try form first and counting the NULLs turns a handful of malformed rows into a number you can inspect, rather than a failed load. (Meridian's view) When every day in a sample is 12 or under, the values alone cannot tell day-first from month-first order, so look for a value with a day above 12 or for the source's own documentation. (Meridian's view)

Sources

  1. [1]Date and time notation in Europesecondary source
  2. [2]Date Format Functions – DuckDBdocumentation

Detection notes

NEW in v2. Common in German-speaking and Eastern European data.

Use it

CLI · DuckDB
$ finetype infer -i "15.01.2024" --mode column
→ datetime.date.dmy_dot

SELECT ft_infer('15.01.2024');
-- → 'datetime.date.dmy_dot'
Cast expression
strptime({col}, '%d.%m.%Y')::DATE
-- Format: %d.%m.%Y

Schema

https://meridian.online/schemas/datetime.date.dmy_dot · Frictionless type date · %d.%m.%Y

finetype taxonomy datetime.date.dmy_dot -o json-schema

Type Registry