In my project , I am generating and storing the Bill (invoice). The date of Bill is ing 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"

In my project , I am generating and storing the Bill (invoice). The date of Bill is ing 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"

• php
• javascript
• mysql

Share Follow asked Jun 18, 2013 at 19:03 VikramVikram 30933 gold badges66 silver badges1919 bronze badges 2

• Is the date column of type DATE or DATETIME? – Ed Gibbs Commented Jun 18, 2013 at 19:08
• you will have to change date format to use date field yyyy-mm-dd – amigura Commented Jun 18, 2013 at 19:08

Add a ment  | 

6 Answers 6

Sorted by: Reset to default 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

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 <.

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

WHERE date BETWEEN '2013-06-15' AND '2013-06-18'

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

WHERE date >= '2013-06-15' AND date < '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.

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 from table a where a.datecolumn between @startdate and @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.

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 bees trivial.

Since you are storing it as text but you want SQL to parse it as a DATE SQL doesn't understand in the first place.

In your example SQL will use TEXT parison rules. So 15-April < 15-Mar > 15-DEC

If you are storing dates in an SQL database you should be storing it as a Date and not as TEXT.