Bash - Convert all instances of date format dd-mm-yyyy to yyyy-mm-dd
sed -r 's/(\d{1,2})-([0-9]{2})-([0-9]{4})/\3-\2-\1/g'
seems to do the trick, there might be some other "smarter" ways ... I don't know
my code:
x="abc,0,2,-2,3-16-1994#xyz,1,2,3,10-09-1994"
echo $x |tr '#' '\n'| sed -r 's/([0-9]{1,2})-([0-9]{2})-([0-9]{4})/\3-\2-\1/g'
produces:
abc,0,2,-2,1994-16-3
xyz,1,2,3,1994-09-10
2 "warnings" for your data ... is a little inconsistent (maybe it was just for the example's sake) ...
- on the first line, the first element of the date is not "zero-padded"
- after looking at the second line, it's not clear if the input data is
dd-mm-yyyy
ormm-dd-yyyy
Later edit: I've first missed the part that padding the day was a requirement, taken this in consideration seems that the following helps
echo $x |tr '#' '\n'| sed -r 's/\b([0-9])-([0-9]{2})-([0-9]{4})\b/\3-\2-0\1/g; s/\b([0-9]{2})-([0-9]{2})-([0-9]{4})\b/\3-\2-\1/g'
practically we have 2 regex one (the first) matches the "dates" where day has only 1 digit and adds the padding "0" when doing the substitution, the other one matches the "dates" that have day with 2 digits and does only the reordering of the elements
got the idea from here https://stackoverflow.com/questions/12129382/add-leading-0-in-sed-substitution, so kudos to the person who answered there
Related videos on Youtube
user1840352
Updated on September 18, 2022Comments
-
user1840352 over 1 year
Yes, before someone jumps up and attack me with a pitchfork, this is a duplicate question, but the others don't work for me, so I'm asking it myself now.
I have a CSV file that have dates somewhere in each entry. Added difficulty to the conversion is that sometimes the dates will have single digits for days. Example entries:
abc,0,2,-2,3-16-1994 xyz,1,2,3,10-09-1994
I want something, preferably sed, to convert those data entries to look like this:
abc,0,2,-2,1994-03-16 xyz,1,2,3,1994-09-10
I've tried:
sed 's|(..)-(..)-(....)|\3-\2-\1|'
But that gives an error and it doesn't really cover the single digit day issues.
I also tried:
awk -F - '{print $3$2$1}'
This actually has somewhat of a desired effect, but then again not really. The awk command converts it, but only the month and year, and instead of putting the date back where it was, it put's the month and year of the beginning of the line, leaving the day portion wherever it originally was.
Any help would be awesome!
Thanks in advance.
Edit
It was rightly pointed out in the comments that I made a mistake with my examples. The dates should be:
abc,0,2,-2,16-03-1994 xyz,1,2,3,2-05-1994
With the desired result being:
abc,0,2,-2,1994-03-16 xyz,1,2,3,1994-05-02
Sorry guys.
-
Admin about 7 yearsCan you explain what about the other solutions didn't work? Are they just not the final format you want, or are they not appropriate for the input you're dealing with, or something else?
-
Admin about 7 years@music2myear I explained what went wrong with the awk solution. The sed command actually threw an error, complaining about something being missing in "RE", an error I've never seen before.
-
Admin about 7 yearsHave you looked further into that error?
-
Admin about 7 yearsAs John C points out in his answer, you say your data are ‘‘dd-mm-yyyy’’, but your example data include ‘‘3-16-1994’’, which means the 3rd day of the 16th month — so your question is flawed.
-
Admin about 7 years@Scott I fixed my question with an edit at the bottom. Sorry for the misleading mistake.
-
Admin about 7 yearsPlease don't fix errors by adding an addendum that says "the above is wrong; it should be X "; fix them by fixing them (in place).
-
-
Scott - Слава Україні about 7 yearsOn the one hand, yeah, the question is flawed. On the other hand, the question says, “sometimes the dates will have single digits for days,” and shows the example of
3
in the input being converted to03
in the output. -
Lohmar ASHAR about 7 yearsit was (very) late in my timezone and I've missed that part of the "requirement", updated the answer to cover it
-
Scott - Слава Україні about 7 yearsSeems to work. If you add
s/\b([0-9]{1,2}-)([1-9]-[0-9]{4})\b/\10\2/;
to the beginning of thesed
command string, it’ll handle single-digit months, too. -
user1840352 about 7 years@LohmarASHAR Thanks, I tried your solution. First the "--r" give me an illegal argument issue. When removing that, I get this error "sed: 1: "s/\b([0-9])-([0-9]{2})- ...": \3 not defined in the RE"
-
Lohmar ASHAR about 7 yearsthere's only 1 dash ("-") before the "r" ... or maybe it's only a typo in your comment, anyway I am using bash WSL (ubuntu in windows ),
sed --version
sayssed (GNU sed) 4.2.2
,sed --help
gives-r, --regexp-extended use extended regular expressions in the script.
. On other servers (some debian and centos) I've found version 4.2.1, and it's still working so ... maybe it wasn't a typo in the comment