Shell Programming and Scripting

I wan to convert any field having date in the file from the follwoing format 5/1/2003 to 2003-05-01. This file is tab delimited. Please help.

Here is a sed(1) script file that should solve your problem. The ^I is just the [Tab] key but it's easier to see.

Code:

s;^\([0-9]\{1,2\}\)/\([0-9]\{1,2\}\)/\([0-9]\{4\}\)^I;\3-^A0\1-^A0\2^I;
s;^I\([0-9]\{1,2\}\)/\([0-9]\{1,2\}\)/\([0-9]\{4\}\)$;^I\3-^A0\1-^A0\2;
s;^I\([0-9]\{1,2\}\)/\([0-9]\{1,2\}\)/\([0-9]\{4\}\)^I;^I\3-^A0\1-^A0\2^I;g
s;^A0\([0-9][0-9]\);\1;g
s;^A;;g

The "^A" is a CTRL-A character NOT a circumflex followed by an uppercase A. It is used instead of, say, a '+' to avoid possible matches in the data. Here is what the script file does:

1 ) Special case where the date string is the first field in the row and is not preceeded by a tab character,
2 ) Special case where the date string is the last field in the row and is not followed by a tab character,
3 ) The general case where you have a tab, date string, and a tab. It is applied to all of the date fields not covered by the two special cases,
4 ) Replace all occurences of ^A0## which would occur if either the day of month or month in year substring was already two digits,
5 ) Get rid of any stray ^A characters left over when the day of month or month in year substring was only one digit

It's confusing but it works. Let me know if you have any questions.

Code:

awk '
  function convdate ( date ) {
   split( date,a,"/" )
   ## Assuming M/D/YYYY; otherwise, swap a[1] and a[2]
   return sprintf( "%4d-%02d-%02d", a[3], a[1], a[2] )
  }
  BEGIN { IFS = OFS = "\t" }
     /[0-9]+\/[0-9]+\/[0-9][0-9][0-9][0-9]/ {
       n = 0
       while ( ++n <= NF ) 
           if ( $n ~ /[0-9]+\/[0-9]+\/[0-9][0-9][0-9][0-9]/ ) $n = convdate($n)
   }
   { print }'