MeridianMERIDIAN

Compact Date (MMDDYYYY)

datetime.date.compact_mdywas date.numeric_mdy in v1

Eight-digit date with no separators, month-first (US ordering).

Worked samples

DuckDB 1.5.5 · finetype 0.6.64

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

Shape

Pattern
^\d{8}$
ValueLengthShape
011520248matches
123120198matches
01152027rejected: one character short

Background

Origin

FineType defines the type in its datetime label definitions as an eight-digit date with no separators, month-first (US ordering).[1] FineType gives the type the format string %m%d%Y, with samples such as 01152024 and 12312019.[1] FineType's registry records numeric_mdy as an alias for the type.[1]

In traditional American usage, dates are written in month–day–year order.[2] The Wikipedia article on United States date notation calls this order middle endian, because it runs in neither increasing nor decreasing order of significance.[2] The American month–day–year order is used in the traditional all-numeric date, such as 01/21/2024.[2] The slash is the most commonly used separator in the American all-numeric date form.[2]

Use

SAS's MMDDYYxw. format writes date values month first, where the x in the format name represents the character that separates the month, day and year.[3] In SAS's MMDDYYxw. format, the value N for x indicates no separator.[3] SAS's documentation for the MMDDYYxw. format gives put day mmddyyn8.; as an example, with the output 05142012.[3]

An all-numeric date can be impossible to understand correctly without knowing the context, because the same digits read differently in day–month–year, month–day–year and year–month–day order.[4] An eight-digit string such as 01022024 is a valid date as month-first and as day-first, so the digits alone cannot settle which order a column uses. (Meridian's view) Look across the whole column for a value whose first two digits exceed 12, or whose middle two do, before trusting either reading, and read the producer's file layout when one exists. (Meridian's view)

Excel automatically removes leading zeros from numbers.[5] Microsoft's guidance is to use Get & Transform (Power Query) to format individual columns as text when you import data.[5]

DuckDB's strptime(text, format) function converts strings to timestamps according to the specified pattern.[6] Python's documentation says that when used with strptime(), the leading zero is optional for formats including %d and %m.[7]

ISO 8601's basic format writes 5 April 1981 as 19810405, with no separators.[8]

Sources

  1. [1]definitions_datetime.yamlFineType
  2. [2]Date and time notation in the United Statessecondary source
  3. [3]MMDDYYx Format :: SAS(R) 9.4 Formats and Informats: Referencedocumentation
  4. [4]List of date formats by countrysecondary source
  5. [5]Keeping leading zeros and large numbersdocumentation
  6. [6]Date Format Functionsdocumentation
  7. [7]datetime — Basic date and time types — Python 3.15.0 documentationdocumentation
  8. [8]ISO 8601secondary source

Detection notes

v1 name: date.numeric_mdy. Same regex as compact_ymd/compact_dmy. COLUMN_MODE_DISAMBIGUATION_TARGET: compact_dmy vs compact_mdy vs compact_ymd

Use it

CLI · DuckDB
$ finetype infer -i "01152024" --mode column
→ datetime.date.compact_mdy

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

Schema

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

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

Aliases

numeric_mdy

Type Registry