Reformatting Dates

[douggy]

I have an Input file that holds dates as follows.

1/2/99 00:00:00
1/3/99 00:00:00
1/4/00 00:00:00
1/4/99 00:00:00
1/5/99 00:00:00
1/6/00 00:00:00

I would like to process this file so I output the dates in DD/MM/YYYY format. I need to have leading zeros where appropriate and the year needs to be 2000 for "00" and 1999 for "99".

eg the last record in the sample should read 01/06/2000.

Can anyone help me out.

Regards

Mark

 

[flogrr]

Hi Mark-

This should do the job for you.


nawk 'BEGIN{FS="[/]+|[ ]+"}

{
$3 ~ /00/ ? year="2000" : year="1999"

if ( $0 ) printf("%02d/%02d/%s %s\n", $1, $2, year, $4)

}' yourfile


Hope this helps!


flogrr
flogr@yahoo.com

 

[douggy]

Thanks so much for you quick reply it's much appreciated. I have a couple of favours to ask.

1) How could we modify the script in order to prefix just "19" or "20" to the relevant years. My full file has a whole selection of years. Sorry I should have mentioned this before.

2) I am quite inexperienced and would really appreciate it if you could comment in what each section of the code does.

I hope this is not to much of a problem for you and thanks again.

Regards
Mark

 

[flogrr]

Hi Mark-

The AWK programming language operates on fields and the
text patterns it finds. So the trick is to manipulate
data by using existing field separators (the forward
slash and the space) to group the patterns so that we
can put the data back together in another form more to
our liking.

Thus, the BEGIN statement assigns built-in variable FS
(field separator) a regular expression (RE) that means
one or more forward slashes -OR- one or more spaces.

This is what produces the fields that AWK gives the names
$1 through $4 that you see sprinkled throughout the code.

The next line says if field 3 matches the RE - a zero as
the first character, assign variable *year* the string
"20" and concatenate field 3 to it. If that test fails,
then assign *year* the alternative value.

Next, if the current line ($0) exists (we are not at the
end of the file), then using the print format function,
format the first two fields ($1 and $2) to be decimal
numerals with a field size of 2 characters that are padded
with leading zeros. Field 3 and field 4 are formatted as
strings, substituting *year* for the original $3 content.

Note: The if test for $0 is necessary because AWK's default
action is to print each line as it is unless you code
otherwise.

This small embellishment to the code will handle years
from 1910 thru 2009. After 2009 you're on your own!


nawk 'BEGIN{FS="[/]+|[ ]+"} # set field separator

{
$3 ~ /^0/ ? year="20"$3 : year="19"$3 # test field 3

if ( $0 ) printf("%02d/%02d/%s %s\n", $1, $2, year, $4) # format and print output

}' yourfile


Good Luck with this.


flogrr
flogr@yahoo.com