Date Issue 01/01/2012 (not > then 12/31/2011)

[Failing_Solutions]

Problem: My query is set to get rows with date after x, as of today (January 1st, 2012) my query doesn't recognize 01/01/2012 as greater than day 12/31/2011.

Webpage: http://www.onlinegamekey. com/ MTGT-Auction.php

Execution Code:

<?php ///Setting The Date & Query Range
$today = date("m/d/Y");
$minusday = mktime(0,0,0,date("m"),date("d")-14,date("Y"));
$queryday = date("m/d/Y",$minusday);
///Getting The Data
$quer2=mysql_query("SELECT * FROM auctions WHERE Card_Name ='$cards' AND Day >='$queryday' ORDER BY Price_Per") or die;
?>

 

Additional Notes: My database stores the date in this format mm/dd/yyyy in column `Day` stored as a VARCHAR. 

Testing on the site: Search for Air Elemental this card is new today (Auction ID)543716  (Card Name)Air Elemental (Cards Per Auction)1 (Seller)Edwitdahead (Auction Price)7 (Price Per)7.00 (Date)01/01/2012

 

I would like to avoid changing the database format if possible, and I assume this is likely a common issue so any help pointers or links you can provide to help me fix this is Greatly Appreciated.

 

Thank you

[PFMaBiSmAd]

Read the post at the following link for why you cannot do greater-than/less-than comparisons on dates unless they are in the correct format - http://www.phpfreaks.com/forums/index.php?topic=345557.msg1631267#msg1631267

 

You must store dates in your database using a DATE data type (that's what it is for) and do comparisons between dates that all have the same YYYY-MM-DD format.

[Failing_Solutions]

Okay so it sounds like I have to reformat all my data dumps, truncate my data, re-upload, and recode my page so the date is outputted in the correct pattern instead of  a database 0000-00-00 pattern.

 

Was trying to avoid that.

[PFMaBiSmAd]

You can use mysql's DATE_FORMAT() function in your queries to retrieve the YYYY-MM-DD format into any format that you need. You can also use mysql's STR_TO_DATE() function in your queries to convert any formatted date into a YYYY-MM-DD format when you insert/update data or provide literal dates to be used in queries.

 

Once you have converted your existing values in your database table(s) (it can be done by adding a DATE column, using a single UPDATE query to populate the DATE column from your existing values, then removing the varchar column), other than changing your query statements to use DATE_FORMAT/STR_TO_DATE, you don't need to make any other changes.

[Failing_Solutions]

Thank you very much.

 

I made a table backup, did a SQL replace for all my dates ie (12/31/2011 to 2011-12-31), then converted the column into data type DATE.

 

Changed my lookup functions to pull the correct data

$today = date("Y/m/d");
$minusday = mktime(0,0,0,date("m"),date("d")-14,date("Y"));
$queryday = date("Y/m/d",$minusday);

 

Then used php to pretty up the date.

date('m-d-Y',strtotime($today))

 

Wasn't as bad as I thought, thanks for the help and previous post that put me on the right track!

 

[jcbones]

You can use MySQL's DATE_FORMAT to format the date into how you want, which reduces overhead on PHP(trivial).

 

<?php
$sql = "SELECT DATE_FORMAT(date_column,%m-%d-%Y) FROM table";
?>