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:
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):
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.
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});
}
}
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);
}