MeridianMERIDIAN

DMY Date with Abbreviated Month (dash-separated)

datetime.date.dmy_dash_abbrev

Day-MonthAbbr-Year with dash separators. The Oracle NLS_DATE_FORMAT default. Extremely common in banking and financial data exports. Unambiguous due to named month.

Operations on this type: Parse a date or time

Try a value

DMY Date with Abbreviated Month (dash-separated)'s rule accepts it.

Reads it as 2024-03-04

Use it

Shell
finetype infer -i '15-Jan-2024' --mode column
# → datetime.date.dmy_dash_abbrev
DuckDB
SELECT ft_infer('15-Jan-2024');
-- → 'datetime.date.dmy_dash_abbrev'
Cast template: {col} is your column
strptime({col}, '%d-%b-%Y')::DATE
-- Format: %d-%b-%Y

How finetype reads the samples

DuckDB 1.5.5 · finetype 0.6.66

ValueAs DATE
15-Jan-20242024-01-15
31-Dec-20232023-12-31
01-Jun-20002000-06-01
%d
day of month, two digits
%b
abbreviated month name
%Y
four-digit year

Pattern

Length 10 to 11 characters.

Regular expression
^[0-3]?\d-[A-Z][a-z]{2}-\d{4}$
ValueLengthShape
15-Jan-202411matches
31-Dec-202311matches
01-Jun-200011matches
15-Jan-20210rejected: one character short

Background

Origin

Oracle Database's NLS_DATE_FORMAT parameter defines the default date format used by the TO_CHAR and TO_DATE functions.[1] Oracle documents the default value of NLS_DATE_FORMAT as derived from NLS_TERRITORY.[2] Oracle's MON format element stands for the abbreviated name of the month.[3]

SAS's DATEw. format writes date values in the form ddmmmyy, ddmmmyyyy, or dd-mmm-yyyy.[4] In SAS's DATEw. format, mmm is the first three letters of the month name.[4] SAS's documentation gives 16-MAR-2012 as the output of the DATE11. format.[4]

Use

A day, a three-letter month and a four-digit year are hard to misread: no reader confuses 03-Feb-2024 with the third of some other month, which is a good reason for a source system to choose it. (Meridian's view) In Oracle, the capitalisation of an abbreviation follows the capitalisation of its format element, so 'DAY' produces 'MONDAY' and 'Day' produces 'Monday'.[3] Expect the month in capitals as well as in title case, and check whether your parser and validation rules accept both. (Meridian's view)

Oracle's NLS_DATE_LANGUAGE parameter determines the language of the month abbreviations used by the default date format.[1]

Where you'll meet it

The Reserve Bank of Australia's F11.1 exchange-rate CSV writes its daily observation dates in this form, such as 03-Jan-2023.[5] The Reserve Bank of Australia's F11.1 exchange-rate CSV also writes its publication dates in this form, such as 16-Oct-2026.[5]

Sources

  1. [1]Setting Up a Globalization Support Environmentdocumentation
  2. [2]1.222 NLS_DATE_FORMATdocumentation
  3. [3]Format Modelsdocumentation
  4. [4]DATEw. Formatdocumentation
  5. [5]F11.1 EXCHANGE RATESdataset

How it's detected

Oracle NLS_DATE_FORMAT default (DD-MON-YYYY). High prevalence in enterprise database exports.

Schema

https://meridian.online/schemas/datetime.date.dmy_dash_abbrev · Frictionless type date · %d-%b-%Y

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

Aliases

oracle_date

Type Registry