Mysql date comparison

Databases
1

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)";

:rolleyes:

2

try this –

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

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

4

oh, i see what went wrong

put quotes around the date string

5

did. but still showing 0 :stuck_out_tongue:

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

6

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

7

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

8

And now my query also working :smiley:
Thank you.


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

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:rolleyes:

10

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()