Gossamer Forum
Quote Reply
test date value
apparently dbman doesn't check for valid date entry and SQL sets date to 0000-00-00 automatically and without comment if an invalid entry is made (invalid being not in format 0000-00-00). i would like to validate the date and transform it into format SQL likes before adding/modifying a record. i've tried this code but it doesn't seem to work:
Code:
my @columns = keys %{$self->{db}->cols};
foreach my $column ( @columns ) {
if (uc($self->{db}->{$column}->{type}) eq 'DATE') {
$self->{cgi}->$column = universal_date($self->{cgi}->{$column});
}
}
then here's my universal_date routine (thank you jpdeni):
Code:
sub universal_date {
my ($date) = $_[0];
my (%months) = ("jan" => 1, "feb" => 2, "mar" => 3, "apr" => 4, "may" => 5, "jun" => 6,
"jul" => 7, "aug" => 8, "sep" => 9, "oct" => 10, "nov" => 11,"dec" => 12,
"january" => 1, "february" => 2, "march" => 3, "april" => 4, "june" => 6,
"july" => 7, "august" => 8, "september" => 9, "october" => 10, "november" => 11,
"december" => 12, "sept" => 9);
my ($time);
my ($american_dates) = 1;
$date =~ s/,|\.//g; # delete any commas or periods that might be in there
$date =~ s/\W/-/g; # change any separators into hyphens

if ((defined($months{lc(substr($date,2,3))})) && (length($date) == 9)) { # date format ddMmmyyyy
$day = substr($date,0,2);
$month = $months{lc(substr($date,2,3))};
$year = substr($date,-4);
}
elsif ((defined($months{lc(substr($date,1,3))})) && (length($date) == 8)) { # date format dMmmyyyy
$day = substr($date,0,1);
$month = $months{lc(substr($date,1,3))};
$year = substr($date,-4);
}
elsif ((substr($date,4,2) > 12) && (length($date) == 8)) { # date format ddmmyyyy
$year = substr($date,4,4);
$month = substr($date,2,2);
$day = substr($date,0,2);
if ($american_dates) { #ambiguous date -- American format delicia added 2/26/2008
$day = substr($date,2,2);
$month = substr($date,0,2);
}
}
elsif (($date > 19000000) && (length($date) == 8)) { # date format yyyymmdd
$year = substr($date,0,4);
$month = substr($date,4,2);
$day = substr($date,6,2);
}
else {
$date = lc($date);
@date_part = split /-/,$date;
$year = $date_part[2];
$year = int($year);
if ($year<100) {
if ($year<20) {
$year += 2000;
}
else {
$year += 1900;
}
}
if (defined($months{$date_part[1]})) { #date format dd-Mmm-yyyy
$day = $date_part[0];
$month = $months{$date_part[1]};
}
elsif (defined($months{$date_part[0]})) { #date format Mmm-dd-yyyy
$day = $date_part[1];
$month = $months{$date_part[0]};
}
elsif ($date_part[0] > 12) { #date format dd-mm-yyyy
$day = $date_part[0];
$month=$date_part[1];
}
elsif ($date_part[1] > 12) { #date format mm-dd-yyyy
$day = $date_part[1];
$month=$date_part[0];
}
elsif ($american_dates) { #ambiguous date -- American format
$day = int($date_part[1]);
$month=int($date_part[0]);
}
else { #ambiguous date -- the rest of the world
$day = int($date_part[0]);
$month=int($date_part[1]);
}
}
unless ($day and $month and $year) { return undef; }
($day < 10) and ($day = "0" . int($day));
$year = int($year);
($month < 10) and ($month="0" . int($month));
$time= $year . $month . $day;
if ($time < 10000101) {
return undef;
}

### dd/mm/yyyy to be sure date is valid 01/16/2010

my ($test2) = $day . '/' . $month . '/' . $year .' 0:0:0' ;
unless ($test2 =~

/^((((31\/(0?[13578]|1[02]))|((29|30)\/(0?[1,3-9]|1[0-2])))\/(1[6-9]|[2-9]\d)?\d{2})|(29\/0?2\/(((1[6-9]|[2-9]\d)?(0[48]|[24

68][048]|[13579][26])|((16|[2468][048]|[3579][26])00))))|(0?[1-9]|1\d|2[0-8])\/((0?[1-9])|(1[0-2]))\/((1[6-9]|[2-9]\d)?\d{2}

)) (20|21|22|23|[0-1]?\d):[0-5]?\d:[0-5]?\d$/ ) {
return undef;
}

##

return ($time);

}
i set up three DATE fields and entered 08/15/2010, 08-15-2010, 08152010 and none worked. when i entered 20100815, that came back ok as 2010-08-15. my universal_date routine worked in flatfile dbman so i'm thinking i'm not passing the value of date entry correctly to the routine.
Subject Author Views Date
Thread test date value delicia 4121 Aug 15, 2010, 10:42 AM
Post Re: [delicia] test date value
delicia 3842 Dec 9, 2010, 9:47 AM