Find and replace with reordered date format in notepad++

Question

I have a file with a few thousand rows to be added to a MySQL database. There are date values in the rows which are in the dd-mm-yyyy format but I need them to be in the yyyy-mm-dd format.

E.g., '11-04-2010', needs to become '2010-04-11', in every row.

Is there a simple way to do this in notepad++ or another text editor?

Solution 1

You can do this with Textpad:

Find: ([0-9]+)-+([0-9]+)-+([0-9]+)

Replace: \3-\2-\1

Solution 2

To make sure you only reorder wrong formats (in case you have mixed formats from merging databases), use this:

([0-9]{2})-+([0-9]{2})-+([0-9]{4})

This searches for (four digits, dash, two digits, dash, two digits).

In an regex capable editor like notepad++, replace it with this:

\3-\2-\1

In a tool like libre office, you need to replace it with this:

$3-$2-$1 

Edit: I wrote a blogpost about this as it seems to be a common problem: http://bytethinker.com/blog/correct-date-format-with-notepad-and-regex

Solution 3

Used Notepad++ to change mm/dd/yyyy to yyyy/mm/dd in several lines of a text file. Script was saved as a macro for next file.

Find: ([0-9]{2})/+([0-9]{2})/+([0-9]{4}) Replace: \3/\1/\2

Solution 4

Another robust pattern that works on ISO date format values like "2010-11-30T00:00:00.266Z "

Capturing groups of various elements of an ISO date representation

"(\d{4})-(\d{1,2})-(\d{1,2})T([0-2]\d):([0-5]\d):([0-5]\d)(?:.\d+)?Z?\s?"

In total six groups are being captured in this pattern, seventh group in the end containing '?:' is non-capturing, meaning it does not keep any value recorded by the group, simply ignores them.

The six groups we have captured have numbers based on positions such as \1 \2 \3... upto \6.

Replacement pattern to change that date into Database supported date format mostly used in Oracle values like - "11/30/2010 00:00:00 AM"

"\2/\3/\1 \4:\5:\6 AM"

It works well with Notepad++ Good Luck!

View more solutions

Comments

[kaushik]

I think you can be more specific by using ([0-9]{4})-+([0-9]{2})-+([0-9]{2}). That \3-\2-\1 was totally new to me and helpful . +1 for that.

[Vignesh Subramanian]

is there something like this for javascript ?