In Oracle, TO_DATE function converts a string value to DATE data type value using the specified format. In MariaDB, you can use STR_TO_DATE function. Note that the TO_DATE and STR_TO_DATE format strings are different.
Oracle:
-- Specify a datetime string literal and its exact format SELECT TO_DATE('2017-12-20', 'YYYY-MM-DD') FROM dual;
MariaDB:
-- Specify a datetime string literal and its exact format SELECT STR_TO_DATE('2017-12-20', '%Y-%m-%d');
You can use SQLines SQL Converter to convert Oracle TO_DATE function to STR_TO_DATE function in MariaDB that maps the format specifiers as follows:
Oracle TO_DATE | MariaDB STR_TO_DATE | |
YYYY | 4-digit year | %Y |
YY | 2-digit year | %y |
RRRR | 2 or 4-digit year, 20th century for 00-49 | %Y |
RR | 2-digit year, 20th century for 00-49 | %y |
MON | Abbreviated month (Jan - Dec) | %b |
MONTH | Month name (January - December) | %M |
MM | Month (1 - 12) | %m |
DY | Abbreviated day (Sun - Sat) | %a |
DD | Day (1 - 31) | %d |
HH24 | Hour (0 - 23) | %H |
HH or HH12 | Hour (1 - 12) | %h |
MI | Minutes (0 - 59) | %i |
SS | Seconds (0 - 59) | %s |
Conversion examples:
Oracle | MariaDB | |
1 | TO_DATE('2017-12-20', 'YYYY-MM-DD') | STR_TO_DATE('2017-12-20', '%Y-%m-%d) |
2 | TO_DATE('20/12/17', 'DD/MM/RR') | STR_TO_DATE('20/12/17','%d/%m/%y') |
3 | TO_DATE('20171220', 'YYYYMMDD') | STR_TO_DATE('20171220', '%Y%m%d') |
If a string literal matches the default DATE format string defined in Oracle, you can use TO_DATE function to convert a string to DATE without specifying the format string:
Oracle:
-- Let's determine the current default DATE format: SELECT value FROM nls_database_parameters WHERE parameter = 'NLS_DATE_FORMAT'; # DD-MON-RR -- So we can convert '21-DEC-17' to DATE without specifying format SELECT TO_DATE('21-DEC-17') FROM dual; # 21-DEC-17
Note that if NLS_DATE_FORMAT is set to 'YYYY-MM-DD', 'YYYY/MM/DD' or 'YYYY.MM.DD' Oracle TO_DATE recognizes various delimiters:
Oracle:
-- Set default format to YYYY-MM-DD ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD'; -- This format matches NLS_DATE_FORMAT SELECT TO_DATE('2021-09-15') FROM dual; # 2021-09-15 -- But other delimiters are also recognized SELECT TO_DATE('2021/09/15') FROM dual; # 2021-09-15 SELECT TO_DATE('2021.09.15') FROM dual; # 2021-09-15 -- Including YYYYMMDD format SELECT TO_DATE('20210915') FROM dual; # 2021-09-15
But when another format is specified Oracle allows only values matching the format:
Oracle:
-- Set default format to DD-MON-YYYY ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY'; SELECT TO_DATE('2021-09-15') FROM dual; # ORA-01861: literal does not match format string SELECT TO_DATE('2021/09/15') FROM dual; # ORA-01861: literal does not match format string SELECT TO_DATE('2021.09.15') FROM dual; # ORA-01861: literal does not match format string SELECT TO_DATE('20210915') FROM dual; # ORA-01861: literal does not match format string
In MariaDB, for some date formats you can also use CAST AS DATETIME function without specifying the date format, for other format you have to use STR_TO_DATE function and specify the format explicitly:
MariaDB:
-- MariaDB will not cast this string to datetime SELECT CAST('21-DEC-17' AS DATETIME); # NULL -- So you have to specify the format SELECT STR_TO_DATE('21-DEC-17', '%d-%b-%y'); # 2017-12-21 -- But the following formats are recognized: SELECT CAST('2017-12-21' AS DATETIME); # 2017-12-21 00:00:00 SELECT CAST('20171221' AS DATETIME); # 2017-12-21 00:00:00 SELECT CAST('2017/12/21' AS DATETIME); # 2017-12-21 00:00:00 SELECT CAST('2017.12.21' AS DATETIME); # 2017-12-21 00:00:00
For more information, see Oracle to MariaDB Migration.