Date filtering

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Date filtering

Post 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?
User avatar
neophyte
DevNet Resident
Posts: 1537
Joined: Tue Jan 20, 2004 4:58 pm
Location: Minnesota

Post 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...
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post 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.
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post by John Cartwright »

Hrmm I guess their unix timestamp functions are quite clever, seemed to work fine :)

Thanks
User avatar
Burrito
Spockulator
Posts: 4715
Joined: Wed Feb 04, 2004 8:15 pm
Location: Eden, Utah

Post by Burrito »

also, take a look at cast()
Post Reply