# Compact Date (YYYYMMDD)

Key: `datetime.date.compact_ymd`

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

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

Format: `%Y%m%d`

Samples: `20240115`, `20191231`

## Background

### Origin

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

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

### Use

SQL Server's CONVERT function labels date style 112 as ISO and gives its format as yyyyMMdd.[[2]](https://learn.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql) SQL Server's date style 12 writes the same order with a two-digit year, as yyMMdd.[[2]](https://learn.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql) Microsoft's datetime documentation lists yyyyMMdd among the ISO 8601 string literal formats for SQL Server's datetime type.[[3]](https://learn.microsoft.com/en-us/sql/t-sql/data-types/datetime-transact-sql) Microsoft's datetime documentation says the ISO 8601 format isn't affected by the SET DATEFORMAT or SET LANGUAGE setting.[[3]](https://learn.microsoft.com/en-us/sql/t-sql/data-types/datetime-transact-sql)

The General Transit Feed Specification defines its Date field type as a service day in the YYYYMMDD format.[[4]](https://gtfs.org/documentation/schedule/reference/) BigQuery's documentation describes table sharding as storing data in multiple tables, using a naming prefix such as \[PREFIX\]\_YYYYMMDD.[[5]](https://docs.cloud.google.com/bigquery/docs/partitioned-tables) BigQuery's documentation recommends partitioning over table sharding, because partitioned tables perform better.[[5]](https://docs.cloud.google.com/bigquery/docs/partitioned-tables)

DuckDB's documentation points to the try\_strptime function to return NULL on failure.[[6]](https://duckdb.org/docs/current/sql/functions/dateformat.html) 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]](https://www.sec.gov/files/aqfs.pdf)

## Sources

1. [ISO 8601](https://en.wikipedia.org/wiki/ISO_8601) — secondary source
2. [CAST and CONVERT (Transact-SQL) - SQL Server \| Microsoft Learn](https://learn.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql) — documentation
3. [datetime (Transact-SQL) - SQL Server \| Microsoft Learn](https://learn.microsoft.com/en-us/sql/t-sql/data-types/datetime-transact-sql) — documentation
4. [Reference - General Transit Feed Specification](https://gtfs.org/documentation/schedule/reference/) — standard
5. [Introduction to partitioned tables](https://docs.cloud.google.com/bigquery/docs/partitioned-tables) — documentation
6. [Date Format Functions](https://duckdb.org/docs/current/sql/functions/dateformat.html) — documentation
7. [Financial Statement Data Sets](https://www.sec.gov/files/aqfs.pdf) — organisation