Master MySQL: Converting Between Strings and Dates Made Simple

String Dates in the Database

When a MySQL column stores dates as strings, two scenarios arise: the string follows the standard yyyy-mm-dd or yyyy-mm-dd HH:mm:ss format, which can be queried directly, or it uses a non‑padded format such as 2020-3-15, which leads to incorrect lexical comparisons.

select * from tb_user where date_time > '2020-03-15'

If the stored string lacks leading zeros, the comparison may treat 2020-3-15 as greater than 2020-10-15 because the month part is compared as a string.

Using date_format for Query Conditions

To handle non‑standard string dates, wrap the column with date_format to normalize it before comparison:

select * from tb_user where date_format(date_time,'%Y-%m-%d') > '2020-03-15'

Although functional, this approach forces MySQL to format each row before filtering, which can degrade performance. Therefore, storing dates in proper date/datetime types is recommended.

Converting Result Dates

When the query returns dates in a compact format like yyyy-m-d (e.g., 2020-1-1), use str_to_date to convert the string to a true datetime value:

select str_to_date(date_time,'%Y-%m-%d %H:%i:%s')

Placeholder Meaning Table

The format specifiers used in date_format and str_to_date are:

%Y – year (four digits)

%c – month (numeric, no leading zero)

%d – day of month (two digits)

%H – hour (00‑23)

%i – minutes

%s – seconds

By applying these functions correctly, you can reliably compare and display dates stored as strings in MySQL.