CODEWITHSUNDEEP
phpmysql
sophie
sophie

Reputation: 1531

Select Where clause with specific date format?

My table have data structure like this

    cate_id  task_id   date_start              date_end                other
         34     14   2012-06-27 10:21:39    2012-06-27 10:21:42     Volume
         34     14   2012-06-27 10:21:42    2016-01-01 00:00:00     Volume




UPDATE tbl SET other ='new'
WHERE task_id =14 
AND cate_id=34
AND DATE_FORMAT('date_start','%Y-%m-%d')='2012-06-27' 
AND DATE_FORMAT('date_end','%Y-%m-%d')='2016-01-01';

My goal is only compare the date with format '%Y-%m-%d' not whole the value.

Can do like the above mysql Script? Because it succeed executed sql script ,but does not update the col that I have specific? Anyone could tell me please? thanks

Upvotes: 3

Views: 12285

Answers (2)

Olivier Coilland
Olivier Coilland

Reputation: 3096

Hum...

Try removing the quotes around date_start and date_end:

AND DATE_FORMAT(date_start,'%Y-%m-%d')='2012-06-27' 
AND DATE_FORMAT(date_end,'%Y-%m-%d')='2016-01-01';

Upvotes: 9

Venu
Venu

Reputation: 7279

Try using date(date_start) and date(date_end) instead of date format

UPDATE tbl SET other ='new'
WHERE task_id =14 
AND cate_id=34
AND date(date_start)='2012-06-27' 
AND date(date_end)='2016-01-01';

FYI: function_date

Upvotes: 0

Related Questions