--- /dev/null
+#!/usr/bin/perl
+
+use strict;
+use warnings;
+use Date::Parse;
+use Date::Format;
+use FS::UID qw(adminsuidsetup);
+use FS::Record;
+
+my @date_fields = (
+ 'Circuit Ordered Date',
+ 'Circuit Due Date (s)',
+ 'Install Date',
+ 'Site Audit Date',
+ 'LOCAL PORT COMPLETE',
+ 'TF PORTING COMPLETE',
+ '411 Submission',
+ 'Billed in Freeside',
+ 'Billed in Quickbooks',
+);
+#@date_fields = ( 'Custom thingie' );
+
+my $dbh = adminsuidsetup(shift) or die "Usage: rt-update-customfield-dates username\n";
+
+foreach my $date_field ( @date_fields ) {
+
+ my $cf_sql = 'SELECT id FROM CustomFields where name = '. $dbh->quote($date_field);
+ my $cf_sth = $dbh->prepare($cf_sql) or die $dbh->errstr;
+ $cf_sth->execute or die $cf_sth->errstr;
+ my $result = $cf_sth->fetchrow_arrayref
+ or do { warn "$date_field not found; skipping\n"; next };
+ my $customfield_id = $result->[0];
+
+ my $ocfv_sql = "SELECT id, content FROM ObjectCustomFieldValues WHERE customfield = $customfield_id and content !~ '^[0-9]+\$' ";
+ my $ocfv_sth = $dbh->prepare($ocfv_sql) or die $dbh->errstr;
+ $ocfv_sth->execute or die $ocfv_sth->errstr;
+
+ while (my $row = $ocfv_sth->fetchrow_arrayref) {
+
+ my($id, $content) = @$row;
+
+ my $origcontent = $content;
+
+ #April 21 KW / April 21 Mont
+ $content =~ s/^April (\d\d) [a-zA-Z]+$/April $1/;
+
+ #SAL April 29 / other May 3
+ $content =~ s/^[a-zA-Z]+ (April|May) (\d\d?)$/$1 $2/;
+
+ #things like "July 8/2010 and "JUNE 24/10" are not doing what we want
+ $content =~ s/^(June|July) (\d\d?)\/(20)?10$/$1 $2, 2010/i;
+
+ #28/04/2010
+ $content =~ s{^(2\d|1[3-9])/(0\d)/2010$}{$2/$1/2010};
+
+ my $unixdate = str2time($content); #current timezone is what we want here
+
+ #things like "DONE"/"ORDERED" are returning a 0 here.. should stay blank
+ my $prettynew = $unixdate ? time2str('%Y-%m-%d %T', $unixdate, 'GMT') : '';
+
+ print "$id: $origcontent -> $prettynew \n" unless $content =~ qr(^0\d/\d\d/2010$);
+
+ my $update_sql =
+ "UPDATE ObjectCustomFieldValues SET content = '$prettynew'".
+ " WHERE id = $id";
+
+ my $update_sth = $dbh->prepare($update_sql) or die $dbh->errstr;
+ $update_sth->execute or die $update_sth->errstr;
+ $dbh->commit or die $dbh->errstr;
+
+ }
+
+}