MDY Date (dash-separated)

Worked samples

DuckDB 1.5.5 · finetype 0.6.64

Shape

Told apart

Background

Origin

In traditional American usage, dates are written in month–day–year order.^[1] Wikipedia describes the American month–day–year order as middle endian, since it follows neither increasing nor decreasing order of significance.^[1] The slash is the most commonly used separator in the all-numeric American date.^[1] According to Wikipedia, the hyphen and the period have also emerged as separators in the all-numeric American date recently, due to globalisation.^[1]

Microsoft SQL Server's CONVERT function lists style 110, under the standard USA, as MM-dd-yyyy.^[2] SQL Server's style 10, also listed under USA, is the two-digit-year form MM-dd-yy.^[2] FineType labels the dash-separated month-day-year date with a four-digit year datetime.date.mdy_dash, with the format string %m-%d-%Y.^[3]

Use

Wikipedia reports that the Chicago Manual of Style discourages all-numeric dates other than the ISO 8601 year-month-day form, as they are not comprehensible to readers outside the United States.^[1] Wikipedia notes that American date styles have influenced date notation in Canada, creating confusion in international commerce.^[1] Microsoft SQL Server's style 105, listed as Italian, writes dates as dd-MM-yyyy.^[2] A dash-separated date such as 06-01-2000 is valid read either way round, so a single value cannot tell you whether a file is month-first or day-first. (Meridian's view) Scan the whole column before choosing: a first part above 12 means day-first, a second part above 12 means month-first, and a column with neither stays ambiguous until you ask whoever produced it. (Meridian's view)

In pandas, the dayfirst parameter of to_datetime defaults to False.^[4] The pandas documentation says that with dayfirst set to True, "10/11/12" is parsed as 2012-11-10.^[4] The pandas documentation warns that dayfirst=True is not strict, but will prefer to parse with day first.^[4] Give the parser an explicit format such as %m-%d-%Y rather than letting it guess the order. (Meridian's view)

In DuckDB's format specifiers, %m is the month as a zero-padded decimal number.^[5] In DuckDB's format specifiers, %d is the day of the month as a zero-padded decimal.^[5] DuckDB's strptime function throws an error on failure.^[5] DuckDB's documentation points to try_strptime for returning NULL on failure.^[5] DuckDB accepts a DATEFORMAT when parsing CSV files, either in the COPY statement or in the read_csv function.^[5]

Wikipedia notes that year-month-day dates such as ISO 8601's YYYY-MM-DD list chronologically when sorted alphanumerically.^[1] Convert month-day-year strings to a real date type, or to ISO 8601 text, before you sort or store them. (Meridian's view)

Sources

1. [1]Date and time notation in the United Statessecondary source
2. [2]CAST and CONVERT (Transact-SQL) - SQL Server | Microsoft Learndocumentation
3. [3]definitions_datetime.yamlFineType
4. [4]pandas.to_datetime — pandas 3.0.6 documentationdocumentation
5. [5]Date Format Functions – DuckDBdocumentation

Detection notes

Use it

Schema