[SOLVED] How to replace and format a date in a string with sed and date command?

Issue

I have a string like "M;12345678900000;Z;;FEWO;F;010622;0.0;;;1.00;0.00;1.25;Y;;;XXX", where the seventh field is a date with ddmmyy format. I need to replace this field with a date in the same format, but 3 days earlier.

I tried the following:

echo "M;12345678900000;Z;;FEWO;F;010622;0.0;;;1.00;0.00;1.25;Y;;;XXX" | sed "s/;\([0-3][0-9][0-1][0-9]2[0-9]\);/;$(date -d "$(date -d \1 '+%d%m%y') - 3 days" '+%d%m%y');/"

The problem is, that i can use a reference (\1) or a script ($(date ..)), but i can't create a working example where both are working. I think it is a problem with quotes, but i'm also unable to escape them properly. Also switches for sed like -E, -e, -r won't help me either. I'd like to see a working sed example, but i'm also thinking to solve this with awk.

Any help appreciated.

Solution

awk would probably be a better candidate but as you ask for a sed solution...

With GNU sed you can execute the result of a successful substitution with the shell and replace the pattern space with the result, thanks to the e flag of the substitute command. Example with the date utility from GNU coreutils if your line does not contain date formatting controls and no quotes:

$ printf 'M;12345678900000;Z;;FEWO;F;010622;0.0;;;1.00;0.00;1.25;Y;;;XXX' |
sed -E 's/(([^;]*;){6})(..)(..)(..)(.*)/date -d "\5\4\3 -3 days" +"\1%d%m%y\6"/e'
M;12345678900000;Z;;FEWO;F;290522;0.0;;;1.00;0.00;1.25;Y;;;XXX

This substitutes the input line with the date command that produces your expected output. With your example:

date -d "220601 -3 days" +"M;12345678900000;Z;;FEWO;F;%d%m%y;0.0;;;1.00;0.00;1.25;Y;;;XXX"

Note the reordering from day-month-year to year-month-day. It is then executed by the shell thanks to the e flag.

If the rest of the line could be interpreted as formatting commands by date we need something a bit more complex:

$ printf 'M;12345678900000;Z;;FEWO;F;010622;0.0;;;1.00;0.00;1.25;Y;;;XXX' |
sed -E 'h
s/([^;]*;){6}(..)(..)(..).*/date -d "\4\3\2 -3 days" +%d%m%y/e
G;s/(.*)\n(([^;]*;){6}).{6}(.*)/\2\1\4/'
M;12345678900000;Z;;FEWO;F;290522;0.0;;;1.00;0.00;1.25;Y;;;XXX

• Copy the pattern space containing the input line to the hold space (h).

• Substitute the full line with date -d "yymmdd -3 days" +%d%m%y where yymmdd is the date in year-month-day format (220601 in your example). The result is executed by the shell and the pattern space is replaced with the result thanks to the e flag. In your example it now contains 290522.

• Append a newline and the hold space to the pattern space (G). At this point the pattern space contains (with your example):

  290522\nM;12345678900000;Z;;FEWO;F;010622;0.0;;;1.00;0.00;1.25;Y;;;XXX
  

• Finally substitute the pattern space with the final format you want.

Answered By - Renaud Pacalet

Answer Checked By - Clifford M. (WPSolving Volunteer)