MariaDB TO_DATE(): Parse Dates with Format Masks

MariaDB TO_DATE() parses a character string with an Oracle-style date and time format model and returns a DATETIME value. It is available from MariaDB 12.3 as part of Oracle compatibility; it is not the same format syntax as STR_TO_DATE().

Syntax

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

• string_expression: the input text to parse.
• fallback_string: optional text to use when conversion of the input fails.
• format_string: required Oracle-style format model that describes the input.
• nls_format_string: optional constant string for supported settings such as NLS_DATE_LANGUAGE or NLS_CALENDAR.

Parse a date or datetime

Use format elements such as YYYY, MM, and DD for a date. Add time elements such as HH24, MI, and SS when the input includes a time:

SELECT TO_DATE(
    '2026-02-05 14:30:45',
    'YYYY-MM-DD HH24:MI:SS'
);

The function returns a datetime value:

2026-02-05 14:30:45

Fractional seconds can use FF with a precision from 1 to 6:

SELECT TO_DATE(
    '2026-02-05 14:30:45.123',
    'YYYY-MM-DD HH24:MI:SS.FF3'
);

If the format model omits date or time components, MariaDB may use the current datetime for those components. Include all components needed for a deterministic result.

Use a fallback for conversion errors

DEFAULT ... ON CONVERSION ERROR supplies an alternate input when the original string cannot be parsed with the format model:

SELECT TO_DATE(
    'not-a-date' DEFAULT '2000-01-01' ON CONVERSION ERROR,
    'YYYY-MM-DD'
);

Choose a fallback string that matches the same format model.

TO_DATE() versus STR_TO_DATE()

Both functions parse text, but use different format syntaxes:

Choose the format model for the function you call. TO_DATE() supports a subset of Oracle’s format elements; for example, MariaDB does not support the Oracle D day-of-week element. Check MariaDB’s TO_DATE() reference for the supported and unsupported elements, locale options, and behavior that differs from Oracle.

See also

• MariaDB STR_TO_DATE() for MariaDB’s percent-based format specifiers.
• MariaDB TO_CHAR() for formatting date and time values as text.
• MariaDB date and time functions for the function index.

Official sources: MariaDB TO_DATE() reference, TO_CHAR() reference, and MariaDB 12.3 release notes.