?
Solved

find results between 2 UK MySQL dates

Posted on 2009-02-09
3
Medium Priority
?
287 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

0
Comment
Question by:cheshirefire
2 Comments
 
LVL 111

Accepted Solution

by:
Ray Paseur earned 500 total points
ID: 23591234
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
0
 

Assisted Solution

by:Kogelet
Kogelet earned 500 total points
ID: 23593417
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

0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn the basics of jQuery, including how to invoke it on a web page. Reference your jQuery libraries: (CODE) Include your new external js/jQuery file: (CODE) Write your first lines of code to setup your site for jQuery.: (CODE)
Suggested Courses
Course of the Month15 days, 10 hours left to enroll

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question