comparing dates in query

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
miramichiphoto
Forum Newbie
Posts: 14
Joined: Thu Mar 17, 2011 1:12 pm

comparing dates in query

Post by miramichiphoto »

The time in the db is a string, formatted like this: yyyy-mm-dd. I want to make sure the date is not in the past. Where is my mistake? Thanks in advance.

Code: Select all

$query = "SELECT * FROM table WHERE strtotime('date')>time() ORDER BY date  ";
Eric!
DevNet Resident
Posts: 1146
Joined: Sun Jun 14, 2009 3:13 pm

Re: comparing dates in query

Post by Eric! »

SELECT * FROM table WHERE date>CURDATE() ORDER BY date;

Depending on how your field (I'm assuming is named "date") is formatted you can use the following:
NOW() =2011-06-19 12:45:34
CURDATE() = 2011-06-19
CURTIME() = 12:45:34
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: comparing dates in query

Post by califdon »

miramichiphoto wrote:The time in the db is a string, formatted like this: yyyy-mm-dd. I want to make sure the date is not in the past. Where is my mistake? Thanks in advance.

Code: Select all

$query = "SELECT * FROM table WHERE strtotime('date')>time() ORDER BY date  ";
Several points:
  • strtotime() is a PHP function and has no meaning in SQL
  • avoid naming a field in a table "date" or any other reserved word
  • if you possibly can, store date and/or time data as datetime data type; it will save you much trouble when you later have to manipulate or compare data
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: comparing dates in query

Post by califdon »

If there is absolutely no possibility of storing your date as datetime data type, you are left with very few options. If you can rely on the stored date string to ALWAYS be strictly yyyy-mm-dd, you could do a simple string comparison against a variable you have set to the current date and formatted appropriately, like this:

Code: Select all

<?php
...
$now = date('Y-m-d');
$query = "SELECT * FROM table WHERE `date` > '$now' ORDER BY date ";
Post Reply