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.
Quote Reply
Re: [delicia] test date value In reply to
got it working:

Code:
#### 12/08/2010 validate date entry
my ($msg) = ''; my ($tmp);
foreach ( keys %{$self->{db}->cols} ) {
next unless ($self->{db}->cols->{$_}->{'type'} eq 'DATE' ); # this correctly gets DATE fields
$tmp = $self->{cgi}->{$_}; #$tmp is the value of the field in the form
if ($tmp) {
($msg,$tmp) = universal_date($self->{cgi}->{$_});
}
if (($msg eq 'bad') && (($tmp eq '0000-00-00') || ($tmp eq '00000000'))) { $msg = ''; }
if (($msg eq 'ok') || (!$msg)) { $msg = ''; $self->{cgi}->{$_} = $tmp; }
else { $msg = qq|<span class="error">Invalid date |; $msg .= $self->{cgi}->{$_}; $msg .= qq|</span><br>|; }
}
( $msg ) and return $self->modify_form($msg);

#####

Code:
sub universal_date {
#----------------------------------------------------------------------
# to translate any type of date entry into SQL-compatible format yyyy-mm-dd

my ($date) = $_[0];
# my ($date) = $tmp;
my ($day,$month,$year,$months,$tmp);

my ($american_dates) = 1;
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);

$date =~ s/,|\.//g; # delete any commas or periods that might be in there
$date =~ s/\W/-/g; # change any separators into hyphens

$date =~ s/-//g;
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);
my (@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 ('bad',$date); }
($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 ('bad',$date);
}

$tmp = $year . '-' . $month . '-' . $day;
# 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]|[246

8][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 ('bad',$tmp);
}


return ('ok',$tmp);
}