**SOLVED** Comparing dates in MySQL

[zq29]

I'm trying to pull all records that are dated in the future, as an example I tried this:[!--sql--][div class=\'sqltop\']SQL[/div][div class=\'sqlmain\'][!--sql1--][span style=\'color:blue;font-weight:bold\']SELECT[/span] * FROM gigs WHERE date > 2006-01-09 ORDER BY date DESC [!--sql2--][/div][!--sql3--]This just pulled all records from the database, so I am assuming I have my syntax incorrect. I tried looking in the MySQL documentation, but that system just frustrates me. Anyone care to set me straight? Thanks in advance.

[fenway]

You're missing the quotes, so the MySQL parser thinks this is a number, determines that it's not a valid number, and silent converts this into an invalid date (0000-00-00) -- so you can see why you get back all of your records. BTW, you should probably use CURDATE() instead.

 

So:

SELECT * FROM gigs WHERE date > '2006-01-09' ORDER BY date DESC

Or:

SELECT * FROM gigs WHERE date > CURDATE() ORDER BY date DESC

Hope that helps.

[obsidian]

right... another thing is if you're ever checking for date ranges, the BETWEEN function is extremely helpful:

 

[!--sql--][div class=\'sqltop\']SQL[/div][div class=\'sqlmain\'][!--sql1--][span style=\'color:blue;font-weight:bold\']SELECT[/span] * FROM tableName WHERE `date` BETWEEN '2005[span style=\'color:orange\']-1-1'[/span] AND '2005[span style=\'color:orange\']-1-31'[/span];

[!--sql2--][/div][!--sql3--]

[zq29]

Ah, missing quotes, that made me red in the face! How embarrasing! Thanks for that, and thanks for the CURDATE() - thats something new I have learnt.

 

Thanks for the tip too Obsidian, might come in handy in the future!