Changing from Excel date format to MySQL date format

UNIX for Beginners Q & A UNIX for Dummies Questions & Answers
1

I have a list of dates in the following format: mm/dd/yyyy and want to change these to the MySQL standard format: yyyy-mm-dd.
The dates in the original file may or may not be zero padded, so April is sometimes "04" and other times simply "4".
This is what I use to change the format:

sed -i '' -e "s_\(..\)/\(..\)/\(....\)_\3-\1-\2_" dates.txt

but because of the dual dots (.), it will not match with single digits. I have also tried:

sed -i '' -e "s_\(\d{1,2}\)/\(\d{1,2}\)/\(\d{4}\)_\3-\1-\2_" dates.txt

but somehow that hasn't worked either and returns the same file. So how do I create the correct regex and what is wrong with the second regex?

2

Try this:

sed 's_\([^/]*\)/\(.*\)/\(.*\)_\3-\2-\1_'

With awk:

awk -F/ '{print $3"-"$2"-"$1}'

Regards

3

Thanks, that worked. I have a penchant for sed, so will use that.