String to Date, Date to String for MySQL/Oracle Database

String and Date Conversion in MySQL and Oracle

	MySQL	Oracle
String to Date	STR_TO_DATE(str, format)	TO_DATE(string, format)
Date to String	DATE_FORMAT(date, format)	TO_CHAR(date, format)

1 String to date

1.1 MySQL String to Date: STR_TO_DATE(str, format)

str: string to be converted
format: the format of the date, '%Y-%m-%d %H:%i:%s', '%Y/%m/%d %H:%i:%s'

The date format is set to - or /, and the returned date is always -.
Most basic date format: '%Y-%m-%d', '%Y/%m/%d'

Reference case:

SELECT STR_TO_DATE('2024-01-10', '%Y-%m-%d') FROM TEST1; -- come (or go) back：2024-01-10
SELECT STR_TO_DATE('2024-01-10 10', '%Y-%m-%d') FROM TEST1; -- come (or go) back：2024-01-10
SELECT STR_TO_DATE('2024-01-10 10:10', '%Y-%m-%d %H:%i:%s') FROM TEST1; -- come (or go) back：2024-01-10 10:10:00
SELECT STR_TO_DATE('2024/01/10', '%Y/%m/%d') FROM TEST1; -- come (or go) back：2024-01-10
SELECT STR_TO_DATE('2024/01/10', '%Y/%m/%d %H:%i:%s') FROM TEST1; -- come (or go) back：2024-01-10 00:00:00
SELECT STR_TO_DATE('2024-01', '%Y-%m') FROM TEST1; -- come (or go) back：NULL
SELECT STR_TO_DATE('2024-01-10', '%Y/%m/%d') FROM TEST1; -- come (or go) back：NULL

Caveats:

(1) The date of the string is - or /, the date format should be set to the same, otherwise it will return null.

(2) Must be accurate to the day, if the date format is only year and month, it will return null.

(3) Hours, minutes and seconds Whether or not to return is controlled by the date format.

1.2 Oracle's String to Date: TO_DATE(string, format)

string: the string to be converted
format: the format of the date.
(‘YYYY-MM-DD hh24:MI:SS’、‘YYYY/MM/DD hh24:MI:SS’)

The date format is set to - or /, and the returned date is always -.

Reference case:

SELECT TO_DATE('2024-01-10', 'YYYY-MM-DD') FROM TEST1; -- come (or go) back：2024-01-10 00:00:00
SELECT TO_DATE('2024-01-10 10:10', 'YYYY-MM-DD hh24:MI:SS') FROM TEST1; -- come (or go) back：2024-01-10 10:10:00
SELECT TO_DATE('2024/01/10', 'YYYY/MM/DD') FROM TEST1; -- come (or go) back：2024-01-10 00:00:00
SELECT TO_DATE('2024/01/10', 'YYYY/MM/DD hh24:MI:SS') FROM TEST1; -- come (or go) back：2024-01-10 00:00:00
SELECT TO_DATE('2024', 'YYYY') FROM TEST1; -- come (or go) back：2024-01-01 00:00:00

Note: The returned date is accurate to the second, regardless of whether the date format is set to seconds or not.

String and date format preferably match the same, the date format is not accurate to the day will also return data, the corresponding value will be the default.

2 Date to String

2.1 MySQL Date to String: DATE_FORMAT(date, format)

date: the date to be converted
format: the format of the date, '%Y-%m-%d %H:%i:%s', '%Y/%m/%d %H:%i:%s'

The date format is set to - or /, and the returned string is - or /.

Most basic date format: '%Y-%m-%d', '%Y/%m/%d'

Reference case:

SELECT DATE_FORMAT('2024-01-10', '%Y-%m-%d') FROM TEST1; -- come (or go) back：2024-01-10
SELECT DATE_FORMAT('2024-01-10 10:10:10', '%Y-%m-%d %H:%i:%s') FROM TEST1; -- come (or go) back：2024-01-10 10:10:10
SELECT DATE_FORMAT('2024/01/10', '%Y/%m/%d') FROM TEST1; -- come (or go) back：2024/01/10
SELECT DATE_FORMAT('2024/01/10', '%Y/%m/%d %H:%i:%s') FROM TEST1; -- come (or go) back：2024/01/10 00:00:00
SELECT DATE_FORMAT('2024-01', '%Y-%m') FROM TEST1; -- come (or go) back：NULL
SELECT DATE_FORMAT('2024-01-10', '%Y/%m/%d') FROM TEST1; -- come (or go) back：2024/01/10

Note: It must be accurate to the day, if the date format only has year and month, it will return null. The returned string is controlled by the date format.

2.2 Oracle's Date to String: TO_CHAR(date, format)

date: the date to be converted
format: the format of the date.
(‘YYYY-MM-DD hh24:MI:SS’、‘YYYY/MM/DD hh24:MI:SS’)

Reference case:

SELECT TO_CHAR(TO_DATE('2024-01-10', 'YYYY-MM-DD'), 'YYYY-MM-DD') FROM TEST1; -- come (or go) back：2024-01-10
SELECT TO_CHAR(TO_DATE('2024-01-10 10:10', 'YYYY-MM-DD hh24:MI'), 'YYYY-MM-DD hh24:MI:SS') FROM TEST1; -- come (or go) back：2024-01-10 10:10:00
SELECT TO_CHAR(TO_DATE('2024/01/10', 'YYYY/MM/DD'), 'YYYY/MM/DD') FROM TEST1; -- come (or go) back：2024/01/10
SELECT TO_CHAR(TO_DATE('2024/01/10', 'YYYY/MM/DD'), 'YYYY/MM/DD hh24:MI:SS') FROM TEST1; -- come (or go) back：2024/01/10 00:00:00
SELECT TO_CHAR(TO_DATE('2024', 'YYYY'), 'YYYY') FROM TEST1; -- come (or go) back：2024

Note: The date to be converted must be a date type and the returned string is controlled by the date format.

summarize

This article on MySQL/Oracle database string to date, date to string is introduced to this article, more related to MySQL/Oracle string and date conversion content, please search my previous articles or continue to browse the following related articles I hope you will support me more in the future!