Search code examples
mysqlsqldatetime

date convert from yyyy-mm-dd to yyy-mm-dd converting year to day

I have migrated data from old DB to new DB where date was taken as varchar, I have migrated data successfully but problem in date . In old DB the format of date is 25-01-02 where 25 is day 01 is month and 02 is year. But my script converted it to 2025-01-02. How can I fix it in my SQL?

TEST case:
DATE                  OUTPUT
2025-05-01            2001-05-25
2002-08-16            2016-08-02
2031-01-01            2001-01-31
2028-08-16            2016-08-28
2001-05-01            2001-05-01
Solution

  • You can it easy convert with STR_TO_DATE like this:

    SELECT STR_TO_DATE('25-01-02', '%d-%m-%y');

    sample

    mysql> SELECT STR_TO_DATE('25-01-02', '%d-%m-%y');
+-------------------------------------+
| STR_TO_DATE('25-01-02', '%d-%m-%y') |
+-------------------------------------+
| 2002-01-25                          |
+-------------------------------------+
1 row in set (0,00 sec)

mysql>

    See the Manual: https://mariadb.com/kb/en/mariadb/str_to_date/