Selecting Records Between Two Dates Using Php From Mysql Database

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

Solution 1:

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

Solution 2:

You should use strtotime PHP function to convert string date to UNIX timestamp format and change MySQL data type for date field to TIMESTAMP.

Than you can do effective queries with > and <.

Solution 3:

If it's a DATE column, you can get all dates between 15 June 2013 and 18 June 2013 (inclusive) using this:

WHEREdateBETWEEN'2013-06-15'AND'2013-06-18'

If it's a DATETIME column, do this instead:

WHEREdate>='2013-06-15'ANDdate<'2013-06-19'

If the date column is indexed, this approach will make sure the indexes are available for optimization. If it isn't indexed, the approach is just as fast as the many other ways you can do this.

Addendum: Just saw the "storing as text" amidst all the other shouted info. Note that this answer applies only if the type is DATE or DATETIME. I'll leave it up because the best answer is to change the column's data type and then use this or one of the other suggested options.

Solution 4:

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

That's a problem. You should store dates as date or datetime data type in MySQL. If you don't care about the time part, date should be sufficient.

If you change your data type to date, then doing:

select x,y,z fromtable a where a.datecolumn between@startdateand@enddate

Should work fine.

If you use a text data type, you would have to cast the column to a date column and then apply your date selection range which is going to be slower due to the cast.

Always store data in the data type that matches its kind. If a date then a date column, if it's text then text or varchar, etc. The presentation layer of your app can worry about the format in which this data is presented to the user.

Solution 5:

You said you were using a TEXT column to store the dates. That's an extremely bad idea. If you switch to a DATE or a DATETIME, then this becomes trivial.