CODEWITHSUNDEEP
phpmysqldate
Preeti Maurya
Preeti Maurya

Reputation: 431

Compare timestamp with date in sql

Hi I am storing date_added with a timestamp in the database .

Now a user wants to see particular transactions on a particular day so he enters the date which is in 2015-05-20 format and the one stored in table is 2015-05-20 13:01:01 format. How can I fetch rows by only considering the date part in the table and not the time. I have tried google and found TO_CHAR which didn't work for me. Also found this link on stackoverflow but didn't work for me.

This is my sql which I get when I die it in the model 

SELECT * FROM `oc_return` r 
LEFT JOIN oc_return_status rs 
ON (r.return_status_id = rs.return_status_id) 
WHERE r.organization_id = '4' 
AND DATE(r.date_added) = DATE(2015-05-06) 
ORDER BY r.return_id DESC LIMIT 0,30

I manipulated the query by removing the DATE() and adding colons, removing them. Also when I there is an entry of it in the table and even when I remove organization_id from the query it shows up

Wild card too didn't work for me. I tried converting to some other format but that too didn't work. I just want that when I fire Query by date it should only check the date part in table and ignore the time part. Please help. Thanks in advance for suggestions/help.

Upvotes: 0

Views: 3788

Answers (1)

lakshman
lakshman

Reputation: 656

use DATE(TIMESTAMP_DATABASE) your sql query. e.g.

SELECT * FROM TBL_NAME WHERE DATE(TIMESTAMP_DATABASE)='$DATE_USER'

Upvotes: 3

Related Questions