Javascript – Selecting records between two dates using PHP from MySql database

javascriptMySQLPHP

In my project , I am generating and storing the Bill (invoice).
The date of Bill is coming to the textbox from the javascript date picker(small pop-up calender) before saving.
The format of the date is : DD-MON-YYYY (18-JUN-2013).

I am using 'Text' data type for storing dates in MySql table.

I have done selecting of records(Previous Bills) from the table by given single date like. . .

$result = mysql_query("SELECT * FROM outward WHERE date='".$date."' ORDER BY billNo");

Now, what i want to do is:
To select records (Bills) between two dates…..

My exact Question is:
Is it possible to query mysql database with this settings or I have to make some changes to select records between 2 dates efficiently ?
How can i achieve this ?

P.s. – Is it effective to use
1. "SELECT * FROM outward WHERE date BETWEEN '" . $from_date . "' AND '" . $to_date . "' ORDER by id DESC"
Or
2. SELECT * FROM outward WHERE date > "15-JUN-2013" and date < "18-JUN-2013"

Best Answer

You could do it in a pure SQL way, but you are going to have to do a full table scan for each query. 

select the_dates,
  STR_TO_DATE(the_dates, '%d-%M-%Y') as converted
from testing
  where STR_TO_DATE(the_dates, '%d-%M-%Y') between '2013-06-20' and '2013-06-23'

Link to SQLFiddle

Related Topic