Date filtering
Posted: Sun Jun 26, 2005 9:15 pm
Okay I have a bit of a problem, I'm building a credit card processor and my client wanted to be able to filter our all transactions between two dates. The only problem is that the date held in the database in varchar.. basically a row would look like (yyyy.mm.dd). My payment gateway stores it that was and it is impossible to change the type of the column, so I'm looking for a workaround.
I understand that it would be a problem if 'mm' or 'dd' only contained 1 digit, so I created this function to pad the numbers with zeros, so it is always the correct amount of digits.
Now when I look have my query with
it sort of works. For example, if I modify the years it will work, but it sort of ignores what day or month I put in. Can anyone think of a way I can get this to work?
I understand that it would be a problem if 'mm' or 'dd' only contained 1 digit, so I created this function to pad the numbers with zeros, so it is always the correct amount of digits.
Code: Select all
function padnumber($num,$length) {
$real_length = strlen($num);
if ($length > $real_length) {
return sprintf("%0".($length - $real_length)."d",$num);
}
return $num;
}
$start_date = padnumber($_POST['s_year'], 4).'.'.padnumber($_POST['s_mon'], 2).'.'.padnumber($_POST['s_mday'], 2);
$end_date = padnumber($_POST['e_year'], 4).'.'.padnumber($_POST['e_mon'], 2).'.'.padnumber($_POST['e_mday'], 2);Code: Select all
$sql .= ' AND REQUEST.TRANS_DATE >= \''.$start_date .'\' AND REQUEST.TRANS_DATE <= \''.$end_date.'\'';