MYSQL DATE COMPARISON

[afridy]

Hello,

My below query does not product any result. i just want to know wether the comparison i am doing is correct.

table : sms_logs

to|date
------------------------------------
05244546 | 2013/04/30 08:46:42 AM
05244536 | 2013/02/10 08:46:42 AM
05244536 | 2013/02/10 08:46:42 AM
-----------------------------------

Date1 = '2013-04-01'; 

Now i want to pull all the sms sent for the past 4 months from the Date1.

           $query1="SELECT * FROM sms_logs 
                    WHERE STR_TO_DATE(LEFT(date,10),'%Y/%m/%d') > DATE_SUB($date1,INTERVAL 180 DAY)"; 

[r937]

try this --

WHERE DATE(date) > $date1 - INTERVAL 180 DAY

[afridy]

try this --

WHERE DATE(date) > $date1 - INTERVAL 180 DAY

0 Result it shows even though there must be records.

sample query out put :
SELECT * FROM sms_logs WHERE DATE(date)>2013-05-01 - INTERVAL 180 DAY

[r937]

oh, i see what went wrong

put quotes around the date string

[afridy]

oh, i see what went wrong

put quotes around the date string

did. but still showing 0

sample query print :
SELECT * FROM sms_logs WHERE DATE('date')>2013-05-01 - INTERVAL 180 DAY

[r937]

no, man, not around the column name (that makes it a string), quotes around the date

2013-05-01 is an arithmetic expression, 2013 minus 5 minus 1, which equals 2007

'2013-05-01' is a proper date string

[afridy]

no, man, not around the column name (that makes it a string), quotes around the date

2013-05-01 is an arithmetic expression, 2013 minus 5 minus 1, which equals 2007

'2013-05-01' is a proper date string

Absolutely! works charm!!
Thank you r973 for your valuable time spend to solve my issue.

[afridy]

And now my query also working
Thank you.

	       $query1="SELECT * FROM sms_logs 
		            WHERE 
                            STR_TO_DATE(LEFT(date,10),'%Y/%m/%d') > DATE_SUB('$date1',INTERVAL 180 DAY)";

[afridy]

try this --

WHERE DATE(date) > $date1 - INTERVAL 180 DAY

i see in mysql manual here that YYYY-MM-DD HH:MM:SS is a valid date format and did not find dates separated with /s are shown as valid examples.

now in your example above your converting a date seperated by /s to DATE(). so i am confused

[r937]

i see in mysql manual here that YYYY-MM-DD HH:MM:SS is a valid date format

yes, that's a valid format for date string values that are used, for example, in sql statements

however, internal date storage format is completely different

you should not be using STR_TO_DATE(), you should be using DATE()