# DMY Date (dot-separated)

Key: `datetime.date.dmy_dot`

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

Page: <https://meridian.online/docs/projects/finetype/types/datetime/date/dmy_dot>

Format: `%d.%m.%Y`

Samples: `15.01.2024`, `31.12.2019`

## Written by

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

## 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]](https://en.wikipedia.org/wiki/Date_and_time_notation_in_Europe) Under the traditional German convention, years could be written with two or four digits.[[1]](https://en.wikipedia.org/wiki/Date_and_time_notation_in_Europe) 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]](https://en.wikipedia.org/wiki/Date_and_time_notation_in_Europe)

In 1995 the DIN 5008 standard, which defines common typographic conventions, replaced the traditional German date notation with the ISO 8601 notation.[[1]](https://en.wikipedia.org/wiki/Date_and_time_notation_in_Europe) Wikipedia says the ISO 8601 form has been becoming the prescribed date format in Germany since 1996-05-01.[[1]](https://en.wikipedia.org/wiki/Date_and_time_notation_in_Europe) 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]](https://en.wikipedia.org/wiki/Date_and_time_notation_in_Europe) 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]](https://en.wikipedia.org/wiki/Date_and_time_notation_in_Europe)

Wikipedia says that in most post-Soviet states the DD.MM.YYYY format is used with dots as separators and with leading zeros.[[1]](https://en.wikipedia.org/wiki/Date_and_time_notation_in_Europe) Wikipedia also notes that official EU documents still tend to use DD.MM.YYYY.[[1]](https://en.wikipedia.org/wiki/Date_and_time_notation_in_Europe)

### 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]](https://en.wikipedia.org/wiki/Date_and_time_notation_in_Europe) 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]](https://duckdb.org/docs/current/sql/functions/dateformat.html) In DuckDB's format specifiers, %d is the day of the month as a zero-padded decimal.[[2]](https://duckdb.org/docs/current/sql/functions/dateformat.html) In DuckDB's format specifiers, %m is the month as a zero-padded decimal number.[[2]](https://duckdb.org/docs/current/sql/functions/dateformat.html) DuckDB's strptime function throws an error on failure.[[2]](https://duckdb.org/docs/current/sql/functions/dateformat.html) DuckDB provides try\_strptime to return NULL on failure instead.[[2]](https://duckdb.org/docs/current/sql/functions/dateformat.html) 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. [Date and time notation in Europe](https://en.wikipedia.org/wiki/Date_and_time_notation_in_Europe) — secondary source
2. [Date Format Functions – DuckDB](https://duckdb.org/docs/current/sql/functions/dateformat.html) — documentation