Page 1 of 1
comparing dates in query
Posted: Sun Jun 19, 2011 6:11 pm
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 ";
Re: comparing dates in query
Posted: Sun Jun 19, 2011 7:47 pm
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
Re: comparing dates in query
Posted: Mon Jun 20, 2011 12:49 pm
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
Re: comparing dates in query
Posted: Mon Jun 20, 2011 12:58 pm
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 ";