TO_DATE

This function is available from MariaDB 12.3.

Syntax

TO_DATE(string_expression [DEFAULT string_expression ON CONVERSION ERROR],
        format_string [,NLS_FORMAT_STRING])

Description

TO_DATE was added for Oracle support.

• format_string has the same format elements as TO_CHAR(), except a few elements that are not supported (see below). TO_DATE() returns a datetime value.

• The allowed separators are the same as for TO_CHAR(): space ( ), tab (\t), and any of !, #, %, ', (, ), *, +, -, ., /, :, ;, <, =, or >.

• & can be used if the next character is not a character in the a-z or A-Z range. text indicates a text string that is used verbatim in the format. You cannot use the double-quote (") as a separator.

• NLS_FORMAT_STRING supports these options: NLS_CALENDAR=GREGORIAN and NLS_DATE_LANGUAGE=language, where language can be one of the following:

  • All MariaDB short locales, like en_AU.

  • The following Oracle language names: ALBANIAN, AMERICAN, ARABIC, BASQUE, BELARUSIAN, BRAZILIAN PORTUGUESE, BULGARIAN, CANADIAN FRENCH, CATALAN, CROATIAN, CYRILLIC SERBIAN, CZECH, DANISH, DUTCH, ENGLISH, ESTONIAN, FINNISH, FRENCH, GERMAN, GREEK, HEBREW, HINDI, HUNGARIAN, ICELANDIC, INDONESIAN, ITALIAN, JAPANESE, KANNADA, KOREAN, LATIN AMERICAN SPANISH, LATVIAN, LITHUANIAN, MACEDONIAN, MALAY, MEXICAN SPANISH, NORWEGIAN, POLISH, PORTUGUESE, ROMANIAN, RUSSIAN, SIMPLIFIED CHINESE, SLOVAK, SLOVENIAN, SPANISH, SWAHILI, SWEDISH, TAMIL, THAI, TRADITIONAL CHINESE, TURKISH, UKRAINIAN, VIETNAMESE

Supported Format Elements

• AD – Anno Domini ("in the year of the Lord")

• AD_DOT – Anno Domini ("in the year of the Lord")

• AM – Meridian indicator (before midday)

• AM_DOT – Meridian indicator (before midday)

• DAY – Name of day

• DD – Day (1-31)

• DDD – Day of year (1-336)

• DY – Abbreviated name of day

• FF[1-6] – Fractional seconds

• HH – Hour (1-12)

• HH12 – Hour (1-12)

• HH24 – Hour (0-23)

• MI – Minutes (0-59)

• MM – Month (1-12)

• MON – Abbreviated name of month

• MONTH – Name of Month

• PM – Meridian indicator (after midday)

• PM_DOT – Meridian indicator (after midday)

• RR – 20th century dates in the 21st century. 2 digits in the 50-99 range are assumed starting from the year 2000, and 0-49 is assumed from 1900.

• RRRR – 20th century dates in the 21st century. 4 digits

• SS – Seconds

• SYYYY – Signed 4-digit year; MariaDB only supports positive years

• Y – 1-digit year

• YY – 2-digit year

• YYY – 3-digit year

• YYYY – 4 digit year

If no datetime is given, the current datetime is used. For example, if 'MM-DD HH-MM-SS' is given, the current year is used. (This is Oracle behavior.)

Unsupported Options

• BC, D, DL, DS, E, EE, FM, FX, RM, SSSSS, TS, TZD, TZH, TZR, X, and SY BC are not supported by MariaDB datetime.

• Most other formats do not make sense in a MariaDB context, as we return datetime with fractions, not as a string.

• D (day-of-week) is not supported as it is not clear how it would map to MariaDB. This element depends on the NLS territory of the session.

• RR only works with 2-digit years. (In Oracle, RR may also work with 4-digit years in some context, but the rules are not clear.)

Extensions and Differences Compared to Oracle

• MariaDB supports FF (fractional seconds). If FF[#] is used, TO_DATE returns a datetime with # of subseconds. If FF is not used, a datetime is returned. A warning (but no error) is issued if the string contains more digits than specified with F(#].

• Names can be shortened to their unique prefixes. For example, both January and Ja work fine.

• No error is issued if the datetime string is shorter than format_string and the next unused character is not a number. This is useful to get a datetime from a mixed set of strings in datetime format. By contrast, Oracle gives an error if the datetime string is too short.

• MariaDB supports short locales as language names.

• NLS_DATE_FORMAT can use double-quote (") and single-quote (') for quoting.

• NLS_DATE_FORMAT must be a constant string. This is to ensure that the server knows which locale to use when executing the function.

Examples

Convert a textual date and time to DATE format:

SELECT TO_DATE('February 5, 2026, 20:56','%Y-%m-%e')
+-----------------------------------------------+
| TO_DATE('February 5, 2026, 20:56','%Y-%m-%e') |
+-----------------------------------------------+
| 2026-02-05                                    |
+-----------------------------------------------+