Jump to content


Photo

**SOLVED** Comparing dates in MySQL


  • Please log in to reply
3 replies to this topic

#1 Kris

Kris
  • Staff Alumni
  • Advanced Member
  • 2,755 posts
  • LocationThe Internet

Posted 09 January 2006 - 11:01 AM

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.

#2 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 09 January 2006 - 01:53 PM

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.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#3 obsidian

obsidian
  • Staff Alumni
  • Advanced Member
  • 3,202 posts
  • LocationSeattle, WA

Posted 09 January 2006 - 02:00 PM

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--]
You can't win, you can't lose, you can't break even... you can't even get out of the game.

<?php
while (count($life->getQuestions()) > 0)
{   $life->study(); } ?>
  LINKS: PHP: Manual MySQL: Manual PostgreSQL: Manual (X)HTML: Validate It! CSS: A List Apart | IE bug fixes | Zen Garden | Validate It! JavaScript: Reference Cards RegEx: Everything RegEx

#4 Kris

Kris
  • Staff Alumni
  • Advanced Member
  • 2,755 posts
  • LocationThe Internet

Posted 09 January 2006 - 02:04 PM

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!




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users