From Oracle TO_DATE to MySQL STR_TO_DATE

I had to import a huge Oracle database dump into MySQL database today. One major problem in doing this was, TO_DATE function of Oracle (used to convert string to date) is not available in MySQL. This post is just to tell you the simple trick that I’ve used to resolved this issue.

The good news is, MySQL have a function STR_TO_DATE for similar purpose, but with different syntax. So I had to replace all the TO_DATE with STR_TO_DATE using the following regular expression (in SublimeText 3) –

Find: TO_DATE\(‘(\d{2})\/(\d{2})\/(\d{4})\s+00:00:00’,\s+’MM/DD/YYYY HH24:MI:SS’\)
Replace: STR_TO_DATE(‘\3-\1-\2’, ‘%Y-%m-%d’)

Then Replace All… Done!

BTW, to see all format characters and syntax of both function, you may check this article.