CODEWITHSUNDEEP
phpmysqldatesearch
blarg
blarg

Reputation: 3893

Searching between dates using substrings in php/mysql

I'm trying to search for all appointments between to dates in an mysql database. The date is in this format. 

 2012-07-26

Here is the code I'm using to select the relevant appointments from drop down selection boxes on another page. 

    AND SUBSTRING(startDate, 6, 2) >= $month
    AND SUBSTRING(startDate, 6, 2) <= $month2 
    AND SUBSTRING(startDate, 9, 2) <= $day 
    AND SUBSTRING(startDate, 9, 2) <= $day2

The problem I have is that if I select a day like 02, then the results will only be days earlier than 02 on any month. Whereas I need something more like;

 SUBSTRING(startDate, 6, 5) <= '$month2'-'$day2'

I'm just not sure how to phrase it correctly.

Upvotes: 0

Views: 1302

Answers (2)

Marc B
Marc B

Reputation: 360662

why not something like:

SELECT ...
WHERE startDate BETWEEN '2012-07-25' AND '2012-07-27'

or if you need a dynamic range

WHERE startDate BETWEEN (now() - INTERVAL 1 WEEK) AND (now() + INTERVAL 1 WEEK)

Doing string operations on date values is usually not necessary. MySQL has a very nice set of date manipulation/retrieval/formatting functions that take care of pretty much everything.

Upvotes: 0

Kermit
Kermit

Reputation: 34055

You should MySQL's date functions instead of SUBSTRING assuming your column is of DATE or DATETIME type.

Relevant date functions:

  • DAY()
  • MONTH()

New code:

AND MONTH(startDate) >= $month
AND MONTH(startDate) <= $month2
AND DAY(startDate) <= $day
AND DAY(startDate) <= $day2

You may also be able to use the BETWEEN operator.

Documentation

Upvotes: 2

Related Questions