MeridianMERIDIAN

Compact Date (YYYYMMDD)

datetime.date.compact_ymdwas date.numeric_ymd in v1

Eight-digit date with no separators, year-first. Common in file naming, partition keys, and data warehousing (e.g. 20240115).

Worked samples

DuckDB 1.5.5 · finetype 0.6.64

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

Shape

Pattern
^\d{4}(0[1-9]|1[0-2])(0[1-9]|[12]\d|3[01])$
ValueLengthShape
202401158matches
201912318matches
20240117rejected: one character short

Background

Origin

ISO 8601 allows a complete calendar date to be written either as YYYY-MM-DD or as YYYYMMDD.[1] Wikipedia's ISO 8601 article gives 5 April 1981 as 1981-04-05 in the extended format or 19810405 in the basic format.[1] The first edition of the ISO 8601 standard was published as ISO 8601:1988 in June 1988.[1]

ISO 8601 notes that "The basic format should be avoided in plain text."[1] ISO 8601 does not allow a year and month alone to be written as YYYYMM, which avoids confusion with the truncated representation YYMMDD.[1] Wikipedia's ISO 8601 article describes the six-digit YYMMDD form as still often used.[1]

Use

SQL Server's CONVERT function labels date style 112 as ISO and gives its format as yyyyMMdd.[2] SQL Server's date style 12 writes the same order with a two-digit year, as yyMMdd.[2] Microsoft's datetime documentation lists yyyyMMdd among the ISO 8601 string literal formats for SQL Server's datetime type.[3] Microsoft's datetime documentation says the ISO 8601 format isn't affected by the SET DATEFORMAT or SET LANGUAGE setting.[3]

The General Transit Feed Specification defines its Date field type as a service day in the YYYYMMDD format.[4] BigQuery's documentation describes table sharding as storing data in multiple tables, using a naming prefix such as [PREFIX]_YYYYMMDD.[5] BigQuery's documentation recommends partitioning over table sharding, because partitioned tables perform better.[5]

DuckDB's documentation points to the try_strptime function to return NULL on failure.[6] Parse the column with an explicit %Y%m%d pattern and a function that returns NULL on failure, then count the NULLs, rather than trusting automatic date detection. (Meridian's view)

An eight-digit integer column is not proof of dates: identifiers, amounts and codes can share the shape, so check that every month and day falls in range before you commit to the type. (Meridian's view) Watch for a six-digit neighbour in the same file: a YYMMDD column alongside a YYYYMMDD one is an easy way to put a date in the wrong century. (Meridian's view)

Where you'll meet it

The SEC's Financial Statement Data Sets documentation defines the ddate field, the end date for a data value, as a DATE written yyyymmdd.[7]

Sources

  1. [1]ISO 8601secondary source
  2. [2]CAST and CONVERT (Transact-SQL) - SQL Server | Microsoft Learndocumentation
  3. [3]datetime (Transact-SQL) - SQL Server | Microsoft Learndocumentation
  4. [4]Reference - General Transit Feed Specificationstandard
  5. [5]Introduction to partitioned tablesdocumentation
  6. [6]Date Format Functionsdocumentation
  7. [7]Financial Statement Data Setsorganisation

Detection notes

v1 name: date.numeric_ymd

Use it

CLI · DuckDB
$ finetype infer -i "20240115" --mode column
→ datetime.date.compact_ymd

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

Schema

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

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

Aliases

numeric_ymd

Type Registry