CODEWITHSUNDEEP
phpmysql
jhodgson4
jhodgson4

Reputation: 1656

Compare MySQL dates when stored as datetime

I have tried several mySQL queries to compare a column of dates stored as datetime. I need to create several different queries depending on what action is taken on the form. Below is an example of the sql I have created:

CREATE OR REPLACE VIEW franchise_filter AS SELECT * FROM `c_data`.`franchise_leads`    WHERE `lead_added` BETWEEN UNIX_TIMESTAMP(DATE_SUB(now(),INTERVAL 3 Months)) AND UNIX_TIMESTAMP(now())

or

CREATE OR REPLACE VIEW franchise_filter AS SELECT * FROM `c_data`.`franchise_leads` WHERE `lead_added` <= 2012-06-27 00:00:00

In the second example the date is supplied by PHP using:

date("Y-m-d 00:00:00", strtotime($date_now, "+6 months"))

Any help would be greatly appreciated.

Upvotes: 0

Views: 103

Answers (3)

Devart
Devart

Reputation: 121902

Try to use a DATE() function, e.g. -

SELECT * FROM table WHERE DATE(date_time_field) <= '2012-06-27';

Also, you can use a CURRENT_DATE() function instead of NOW().

Upvotes: 0

yoda
yoda

Reputation: 10981

BETWEEN NOW() + INTERVAL 3 Months AND NOW()

Upvotes: 0

Eugen Rieck
Eugen Rieck

Reputation: 65264

You were very close: Use

... WHERE `lead_added` <= "2012-06-27 00:00:00"

(mind the quotes) and

... WHERE `lead_added` BETWEEN DATE_SUB(NOW(),INTERVAL 3 MONTH) AND NOW()

Upvotes: 2

Related Questions