We help IT Professionals succeed at work.

find results between 2 UK MySQL dates

Medium Priority
305 Views
Last Modified: 2012-05-06
Hi,

I hope someone can help my headache. Im trying to display all records from a MySQL database which come between 2 dates. Users can enter 2 sets of dates in a PHP form which then pieces together a 'from' date and a 'to' date in the format 30/01/2008.

Ive tried running the following code for the sql query but I either get a page of errors or nothing at all when I know there are records which match.

The date is stored as text in the format 30/01/2009 etc but still no joy.

SELECT costs.cost_invoice_date, costs.cost_job_id, costs.cost_amount_received, costs.cost_payment_method, jobs.job_description FROM costs, jobs WHERE costs.cost_job_id = $n
            AND costs.cost_amount_received != ''
            AND costs.cost_job_id = jobs.job_id
            AND costs.cost_invoice_date >= date_format('$from','dd/mm/yyyy')
            ORDER BY costs.cost_invoice_date ASC";

Can anyone help?
SELECT costs.cost_invoice_date, costs.cost_job_id, costs.cost_amount_received, costs.cost_payment_method, jobs.job_description FROM costs, jobs WHERE costs.cost_job_id = $n 
		AND costs.cost_amount_received != '' 
		AND costs.cost_job_id = jobs.job_id 
		AND costs.cost_invoice_date >= date_format('$from','dd/mm/yyyy')
		ORDER BY costs.cost_invoice_date ASC";

Open in new window

Comment
Watch Question

Most Valuable Expert 2011
Author of the Year 2014
Commented:
For this and other date-related things, you should always store the dates in the DATETIME or DATE format.  Then you can use strtotime() to convert client input into a timestamp and date('c') to convert the timestamp into an ISO-8601 format date.

Queries that are looking for records with dates between two start and end points should probably use "BETWEEN instead of separate tests for greater and less.  If you use EXPLAIN you can see that the separate tests are less efficient.

HTH, ~Ray

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
Commented:
You are using an incorrect syntax for MySQL query.

Please try the following code:



SELECT costs.cost_invoice_date, costs.cost_job_id, costs.cost_amount_received, costs.cost_payment_method, jobs.job_description FROM costs, jobs WHERE costs.cost_job_id = $n 
                AND costs.cost_amount_received != '' 
                AND costs.cost_job_id = jobs.job_id 
                AND costs.cost_invoice_date 
BETWEEN DATE_FORMAT( '30/01/2008', '%d/%M/%Y' )
AND DATE_FORMAT( '30/01/2030', '%d/%M/%Y'  )

Open in new window

Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.