MySQL Query Does Not Recognise Date

Hi,
This is my table structure:
CREATE TABLE IF NOT EXISTS commercial (
id int(6) NOT NULL auto_increment,
date date NOT NULL default ‘0000-00-00’,
location varchar(100) NOT NULL,
property_type varchar(100) NOT NULL,
tenure varchar(50) NOT NULL,
status varchar(50) NOT NULL,
sale_rent varchar(50) NOT NULL,
size decimal(15,2) default ‘0.00’,
price decimal(15,2) default ‘0.00’,
price_psf decimal(15,2) default ‘0.00’,
rooms varchar(5) NOT NULL,
baths varchar(5) NOT NULL,
facilities varchar(150) NOT NULL,
tenure_remaining varchar(5) NOT NULL,
furnishings varchar(150) NOT NULL,
phone_1 varchar(20) NOT NULL,
advertiser varchar(50) NOT NULL,
e_num varchar(20) NOT NULL,
unique varchar(255) NOT NULL,
PRIMARY KEY (id),
UNIQUE KEY unique (unique)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=210966 ;

This is my query which I’ve tried running in phpmyadmin. It does not work because mysql returns the entire 210966 records though there are only a few records which are newer than 2015-01-07
SELECT * FROM commercial WHERE date > 2015-01-07
Why is this happening & how to get the query working properly?

“date” is a reserved word

1 Like

“unique” is also a reservered word

http://dev.mysql.com/doc/refman/5.5/en/reserved-words.html

That lists the reserved words in version 5.5 of PHP, if you’re using a different version, check the page for that version

2 Likes

2015-01-07 is not a date but a numeric expression which is evaluated to 2007. Mysql will implicitly convert this to a date value, ‘0000-00-00’. All your date values are larger than this value, hence all rows are returned. Use a proper date literal in the comparison predicate.

SELECT * FROM commercial WHERE date > date '2015-01-07'
1 Like

Wonderful help & explanation swampBoogie. I appreciate it. Yes, the query works fine now. Have a good day :slight_smile:

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.