Solved: Date: Convert m to mm

Shell Programming and Scripting
1

Hi all,

I've searched through the forum and can't find a thread specific to this question:

I have a CSV file with a date column, the dates I'm given from the original file look like: "m/dd/yyyy" (UNLESS it's a double digit month, like October, where it would then show up as "mm/dd/yyyy"). Is there an easy way where I could add a "0" to the "m" if the month has a single digit?

I know I can grab the column using awk, I just don't know how I can perform the if statement to add the "0" prefix to the month from the value I'd grab (which in this case, would by $4 as far as awk is concerned). Any pointer in the right direction sure would be appreciated, if this is doable.

Thanks!

2

try awk -F, -vOFS=, '$4 ~ /^.\//{$4="0"$4}1' input

mute@thedoctor:~$ cat input
one,two,three,1/12/1999,five
one,two,three,10/12/1999,five
mute@thedoctor:~$ awk -F, -vOFS=, '$4 ~ /^.\//{$4="0"$4}1' input
one,two,three,01/12/1999,five
one,two,three,10/12/1999,five
1 Like
3

Neutron, that was perfect. Thanks so much, I sure appreciate your prompt response :slight_smile:

Just out of curiosity, can you explain to me how that command knows to insert a 0 if the month isn't a double digit? I don't quite follow the logic in that statement. Thank you for your time.

4

The secret is here:

$4 ~ /^.\/

which means that field 4 begins (^) with any character (.) (i.e. a number) followed by a slash (escaped) (\/). If field 4 started with two digits, the match would fail.

$ echo ",3/" | awk -F, '$2 ~ /^.\// { print "Yes" }' 
Yes
$ echo ",03/" | awk -F, '$2 ~ /^.\// { print "Yes" }'
$
1 Like
5 
$4 ~ /^.\//{$4="0"$4}1

$4 ~ Looks only for records number 4 which is the date part
/^.\// The action is only performed in string with one character followed by / char, that excludes 10/ (two digits followed by /)
$4="0"$4 if so, add an extra 0 to the record
1 re-read what just added.

1 Like
6

Aha! I see. Thank you, Scott. Is there a way to mark this thread as "solved"? Thanks!

7

Done.

8

I guess the next question to be: "What about single digit days?" Try this:

awk -F, -vOFS=, \
        '$4 ~ /^.\//    {$4 = "0"$4}
         $4 ~ /\/.\//   {sub (/^[^\/]*\//, "&0", $4)}
         1
        ' file
one,two,three,01/12/1999,five
one,two,three,10/02/1999,five
1 Like
9

Aha, fair enough, thank you Rudi! That was a good spot there :o)