help me understand date formats in query statement

[micah1701]

this statement works fine:

SELECT * FROM table WHERE date_column > '2007-07-24'

 

but this didn't work:

$my_date = date("Y-m-d"); //returns: 2007-07-24

SELECT * FROM table WHERE date_column > '$my_date'

 

the fix was to do this:

$my_date = date("Ymd"); //returns: 20070724

SELECT * FROM table WHERE date_column > '$my_date'

 

So, hand coding hyphens into the date worked fine (I should mention that the data in the table is formatted with hyphens) - BUT - using a variable with a string of the same hyphen formated date didn't work.  The solution was to take the hyphens out when using a variable.

 

Why is this?  Thanks for helping me learn what I seem to be missing!

 

[Wildbug]

(The MySQL date type isn't actually stored with hyphens; it's just formatted that way in output.)  It's very odd that your second example "did not work" -- PHP/MySQL doesn't differentiate between hand-coding and not, so it might have been a quoting issue.  What was the error and full code?

 

You can either enter a date in a string form, "2007-07-24", or in integer form, 20070724.  Many variations of this are legal, such as the string "20070724" or using odd delimiters, "2007^07^24".

 

See the MySQL manual for a full discussion of what date/time formats MySQL allows:  http://dev.mysql.com/doc/refman/5.1/en/datetime.html