Selecting records between two dates using PHP from MySql database

Question

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"

you will have to change date format to use date field yyyy-mm-dd — amigura
– amigura, Commented Jun 18, 2013 at 19:08

Brian Hoover · Accepted Answer · 2013-06-18 19:14:37Z

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

answered Jun 18, 2013 at 19:14

Brian Hoover

7,9652 gold badges31 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Pinoniq Over a year ago

This is the correc answer, but I wouldn't use it since storing a Date as Text makes no sense. And as Brian points out this query is not that fast

Brian Hoover Over a year ago

@Pinoniq - I would hope I would be shot before storing a date field as text :)

Vikram Over a year ago

Here is one problem - I am not storing dates like 2013-06-20 . Its 20-Jun-2013 by using 'Text' data type..

Vikram Over a year ago

I think I have to store dates using Date data type . . .And at the time of storage and retrival ..have to deal with '20-Jun-2013' format..

Brian Hoover Over a year ago

@Vikram - Again, I'm not condoning this, but the SQL posted converts from '20-Jun-2013'. That's in the SQLFiddle

zavg · Accepted Answer · 2013-06-18 20:29:08Z

0

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

edited Jun 18, 2013 at 20:29

answered Jun 18, 2013 at 19:11

zavg

11.2k4 gold badges47 silver badges68 bronze badges

Comments

Ed Gibbs · Accepted Answer · 2013-06-18 19:11:46Z

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.

Icarus · Accepted Answer · 2013-06-18 19:12:14Z

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.

Peter Geer · Accepted Answer · 2013-06-18 19:12:41Z

0

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.

answered Jun 18, 2013 at 19:12

Peter Geer

1,1721 gold badge6 silver badges12 bronze badges

Comments

Pinoniq · Accepted Answer · 2013-06-18 19:15:28Z

0

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

answered Jun 18, 2013 at 19:15

Pinoniq

1,3859 silver badges14 bronze badges

Collectives™ on Stack Overflow

Selecting records between two dates using PHP from MySql database

6 Answers 6

5 Comments

Comments

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

5 Comments

Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related