[PLUG] Date Reformatting in Database Table

[Carlos Konstanski]

On Thu, 8 Oct 2009, Rich Shepard wrote:

> Date: Thu, 8 Oct 2009 13:43:06 -0700 (PDT)
> From: Rich Shepard <rshepard at appl-ecosys.com>
> To: plug at pdxlinux.org
> Subject: [PLUG] Date Reformatting in Database Table
>
>   I downloaded a database from an agency's Web site, and the date formats
> are not that used by SQL (YYYY-MM-DD). Instead, they are in the form of
> D/M/YYYY (or DD/MM/YYYY). Not only that, but the column they are in is the
> second to last of 11 columns; a representative sample is:
>
> 1993-1|Water Quality|WVR|Yamhill, City of|Yamhill|Hamlin|Holt|Npv|NPDES-Waste Discharge Limits|7/6/1993|01993-1|Water Quality|WVR|Yamhill, City
> of|Yamhill|Hamlin|Holt|Npv|NPDES-Waste Discharge Limits|7/6/1993|0
>
>   Is there a practical way to apply sed and/or awk to convert the date
> column above from 7/6/1993 to 1993-06-07?
>
> Rich

You did say »practical«. A perl script would be the most practical way
to deal with this file. I'd want to examine one line of text at a
time, parsing each line on the | characters. The data field would
always be the 19th element of the resulting array (counting from
0). The date would then be parsed on the / characters, the elements
rejuggled, and padded with 0s if neccesary. Then the whole line would
be reassembled with the new date inserted into position 19, and
written to a new file.

I'd give you a sample script here, but that would take all the fun out
if for you.

Carlos