Page 1 of 1

Date filtering

Posted: Sun Jun 26, 2005 9:15 pm
by John Cartwright
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.

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);
Now when I look have my query with

Code: Select all

$sql .= ' AND REQUEST.TRANS_DATE >= \''.$start_date .'\' AND REQUEST.TRANS_DATE <= \''.$end_date.'\'';
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?

Posted: Sun Jun 26, 2005 9:39 pm
by neophyte
I'm not following the problem well. But two functions come to mind.

strtotime() and perhaps converting the column to a unix time stamp. You know like unix_time(date_column) as unix_start. Than you could do straight unix < > comparisons.

Hope this helps...

Posted: Sun Jun 26, 2005 9:48 pm
by John Cartwright
Not exactly sure how I can output the yyyy.mm.dd varchar column a unix timestamp, because it is not in the correct format.

Posted: Sun Jun 26, 2005 10:02 pm
by John Cartwright
Hrmm I guess their unix timestamp functions are quite clever, seemed to work fine :)

Thanks

Posted: Mon Jun 27, 2005 12:01 am
by Burrito
also, take a look at cast()