convert m/d/yyyy to yyyymmdd

convert m/d/yyyy to yyyymmdd

1
NewbieNewbie
1

    Aug 13, 2015#1

    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.

    6,602547
    Grand MasterGrand Master
    6,602547

      Aug 13, 2015#2

      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

      20020202
      20110930
      20031101
      20121020
      Best regards from an UC/UE/UES for Windows user from Austria