Select data from MySQL according to period of time

[Alexancho]

There is a MySQL table with list of users there actions and dates of this actions in dd-mm-yy format. I need to select data from this table according to period of time (picked from calendar, for example, from 01-11-2010 till 30-11-2010). What is the right way to do it?

[justsomeguy]

What data type did you use for the date column?

[Alexancho]

What data type did you use for the date column?

VARCHAR, but i can change it to date or any other.

[birbal]

DATE type take date as YYYY-MM-DD as far i can remember. and your date format is not same as you reffered at your post. you cant change any column data type untill all data of the colum meets the data type criteria. if you want to change it you need to start over again (delete previous data and change the data type).

[Fmdpa]

Yes, the DATE column does take the YYYY-MM-DD format. MySQL has a handful of useful functions that can be used for the DATE/DATETIME column type. I just used one of them recently. I wanted to select rows from the past 60 days. I think the query was something like this:

SELECT * FROM table WHERE DATEDIFF(NOW(), date) > 0 AND DATEDIFF(NOW(), date) < 60

[Alexancho]

Yes, the DATE column does take the YYYY-MM-DD format. MySQL has a handful of useful functions that can be used for the DATE/DATETIME column type. I just used one of them recently. I wanted to select rows from the past 60 days. I think the query was something like this:

SELECT * FROM table WHERE DATEDIFF(NOW(), date) > 0 AND DATEDIFF(NOW(), date) < 60

Thank you. I tried to write something like this, but it doesn't work:

$date1 = '2010-08-10'; // beginning of period of time$date2 = '2010-10-10'; // end of period of time$query = "SELECT * FROM sms_data_after_date WHERE username = '".$username."' WHERE DATEDIFF(date,$date1)>=0 AND DATEDIFF($date2,date)>=0 ORDER by sms_id DESC";

or

$query = "SELECT * FROM sms_data_after_date WHERE username = '".$username."' WHERE DATEDIFF(date,'2010-08-10')>=0 AND DATEDIFF('2010-10-10',date)>=0 ORDER by sms_id DESC";

What's wrong!

[Fmdpa]

You can't use more than one WHERE clause. Instead, use AND/OR conjunctions.

$query = "SELECT * FROM sms_data_after_date WHERE username = '".$username."' AND DATEDIFF(date,'2010-08-10')>=0 AND DATEDIFF('2010-10-10',date)>=0 ORDER by sms_id DESC";

[Alexancho]

Thank you very much!The problem is solved.