convert m/d/yyyy to yyyymmdd

worker17 · Aug 13, 2015#12015-08-13T13:37+00:00

I have some files created by an ODBC source that puts out dates like 9/30/2011, 11/1/2003, 2/2/2002. The format is m/d/yyyy. I am trying to import this into a SQL database but the import routines wants all dates to be yyyymmdd format (20110930, 20031101, 20020202 from above).

The date occurs twice per row, variable row length.

Is there a standard for doing this?

Thanks.

Mofi · Aug 13, 2015#22015-08-13T17:40+00:00

You need to run a macro (or script) with several Perl regular expressions using backreferences to convert all date strings to required format.

Code: Select all

InsertMode
ColumnModeOff
HexOff
Top
PerlReOn
Find MatchCase RegExp "\b([1-9]/)([1-9]/[12][09][0-9][0-9])\b"
Replace All "0\10\2"
Find MatchCase RegExp "\b([1-9]/[0-3][0-9]/[12][09][0-9][0-9])\b"
Replace All "0\1"
Find MatchCase RegExp "\b([01][0-9]/)([1-9]/[12][09][0-9][0-9])\b"
Replace All "\10\2"
Find MatchCase RegExp "\b([01][0-9])/([0-3][0-9])/([12][09][0-9][0-9])\b"
Replace All "\3\1\2"

The first Perl regular expression Replace All finds date strings with a single digit month and a single digit day and inserts the two missing leading zeros.

The second Perl regular expression Replace All finds date strings with a single digit month and a two digit day and inserts the missing leading zero on month.

The third Perl regular expression Replace All finds date strings with a two digit month and a single digit day and inserts the missing leading zero on day.

And the last Perl regular expression Replace All finally changes the date string format for all date strings in file.

So this macro code reformats the date strings

Code: Select all

2/2/2002
9/30/2011
11/1/2003
10/20/2012

to

Code: Select all