[PLUG] Date Reformatting in Database Table

Roderick A. Anderson raanders at cyber-office.net
Thu Oct 8 23:50:25 UTC 2009


Rich Shepard wrote:
>    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?

Is this suppose to be two lines?

Perl OK?

while (<>) {
     chomp;
     my ($f1, $f2, $f3, $f4, $f5, $f6, $f7, $f8, $f9, $f10, $f11) =
     split('\|', $_);
     my ($D, $M, $Y) = split('\/', $f10);
     my $newdate = sprintf("%04d-%02d-%02d" , $Y, $M, $D);
     print join('|', $f1, $f2, $f3, $f4, $f5, $f6, $f7, $f8, $f9, 
$newdate, $f11). "\n";
}

Not elegant but works with the data provided.


\\||/
Rod
-- 
> 
> Rich
> 




More information about the PLUG mailing list