Correct date format with notepad++ and regex
Sometimes, date formats get mixed up. I.e. if you merge databases or import values. Editing them manually may be ok, but is a lot of work whey you have many of wrong values. And errors may occur.
Editing them with a regular expression (regex) may be the easier and faster way, if you know how.
I have a merged table with imported event dates. Unfortunately, they have different formats:
As you can see the year and day of the second entry are different from the first entry. I need the first format.
So here are the steps:
- access database table via phpMyAdmin and export the table of interest (Exportformat .sql is the best in this case). You need to have it locally.
- copy the downloaded file so you have a backup. Important! Only work on copies!
- open the file in notepad++
- ctrl+h should open the "search and replace" dialog window
- set "Regular Expressions" as the search mode (important to enable regex mode)
- enter this into the search input field:
- this will find any numbers that loos like: two digits, dash, two digits, dash, four digits
- enter this into the replace input field:
- this will replace the found blocks in reverse order. Everything inside brackets is a single search expression.
- if you use libreoffice calc and work with .csv instead of .sql, you need to use this replacement code instead:
- with libreoffice, watch out for the automatic cell formatting!
- save the file after you replaced all bad formats
- go to phpMyAdmin and truncate the table of interest. That is why you keep a backup of the file :)
- import the edited file from your harddrive