0

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
Muntasir
  • 798
  • 1
  • 14
  • 24
mks
  • 37
  • 1
  • 10
  • You need to use [DATE_FORMAT](https://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html#function_date-format) – Parag Jadhav Jun 04 '17 at 07:48
  • I have already tried can you modify it! SELECT f_date , CAST( DATE_FORMAT(f_date, '%y-%m-%d') AS CHAR), DATE_FORMAT(CAST( DATE_FORMAT(f_date, '%y-%m-%d') AS CHAR),'%Y-%m-%d') FROM table – mks Jun 04 '17 at 07:51

1 Answers1

1

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/

Bernd Buffen
  • 14,525
  • 2
  • 24
  • 39
  • @Martin - I have add the link, but you can search by google with 2 words : **mysql str-to_date** to find anything – Bernd Buffen Jun 04 '17 at 08:28
  • Yes Google is always an answer, but when the OP reads the answer and wants to know more about the function specified/recommended, it's good to have a link in the answer, else if the OP googles and gets something else that may not be what you intend. – Martin Jun 04 '17 at 08:29
  • I would suggest linking to the *MYSQL* manual as [`Although MariaDB is supposed to be compatible with MySQL, for one reason or the other there are quite a few compatibility issues and different features`](https://softwareengineering.stackexchange.com/questions/120178/whats-the-difference-between-mariadb-and-mysql) – Martin Jun 04 '17 at 08:32
  • @Martin - **YES** there is a difference: MariaDB is the better MySQL and **NO** there is no difference in the functions. MariaDB is a binary replacement from MySQL – Bernd Buffen Jun 04 '17 at 08:32