Gossamer Forum
Home : Products : DBMan : Customization :

Can't sort by date field when searching (help).

Quote Reply
Can't sort by date field when searching (help).
I can't seem to get my database (~3000 records) to sort by one of the date fields correctly. I may have broken something with a mod I installed that changes the date format to mm-dd-yyyy but I'm not Perl literate. Frown

This is my sub get_date:
Code:
sub get_date {
# --------------------------------------------------------
# Returns the date in the format "dd-mmm-yy".
# Warning: If you change the default format, you must also modify the &date_to_unix
# subroutine below which converts your date format into a unix time in seconds for sorting
# purposes.

my ($time1) = $_[0];
($time1) or ($time1 = time());
my ($sec, $min, $hour, $day, $mon, $year, $dweek, $dyear, $daylight) = localtime($time1);
my (@months) = qw!1 2 3 4 5 6 7 8 9 10 11 12!;
($day < 10) and ($day = "0$day");
$year = $year + 1900;
return "$months[$mon]-$day-$year";
}

And this is my sub date_to_unix:
Code:
sub date_to_unix {
# --------------------------------------------------------
# This routine must take your date format and return the time a la UNIX time().
# Some things to be careful about..
# int your values just in case to remove spaces, etc.
# catch the fatal error timelocal will generate if you have a bad date..
# don't forget that the month is indexed from 0!
#
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); $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 ((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);
} 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_part = split(/-/, lc($date));
$year = $date_part[2];
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;
} return ($time);
}

Since I am behind a firewall I had to save a sample of the search results and put them on my personal website. These are the results from a search that should have been sorted by the "date" field in "descending" order:

www.pro-stuff.net/results.html

If anybody can spot a problem in the code that may be causing this I would much appreciate it. I need to have this fixed by Monday for a presentation and I'm running out of time. Smile

Thanks,

Daniel Alexander