Quicker way to do this?

[TEENFRONT]

Hey

 

Im just writing an additon to my invoicing system that returns all the piad invoices for the last tax year. I have a "paid" colum for my invoices which has data like, 01/01/2010 etc.

 

my sql query is like this

 

SELECT * FROM invoices WHERE paid LIKE '%/04/2009' AND paid LIKE '%/05/2009' AND paid LIKE '%/06/2009' ........ AND paid LIKE '%/01/2010' AND paid LIKE '%/02/2010'

 

etc, basically covering everything from 04/2009 to 03/2010 .. but iv got 12 AND LIKE bits in my querry which seems abit overkill, any easier/faster way?

 

Cheers!

[jskywalker]

SELECT * FROM invoices WHERE paid between '2009-04-01' and LAST_DAY('2010-02-01')

[TEENFRONT]

Would that depend on the format of the paid colum?

 

As said the paid date colum is formated like DD/MM/YYYY

 

Cheers!

[jskywalker]

your paid column is a date column, so its not formatted.....

 

but you should use the correct default format for your system.

(my system uses 'yyyy-mm-dd')

[PFMaBiSmAd]

If you are serious about making a real application (something that will be used outside of a programming classroom assignment), your first step will be to store your dates as a DATE data type.

 

As you have discovered by attempting to write the query in your first post in this thread, NOT using the data type that was designed for a specific purpose results in writing more difficult code and queries that take longer to execute.

 

Using a DATE data type will result in less storage requirements, faster queries, and simpler code.

 

You can use the mysql STR_TO_DATE() function directly in a query to INSERT any starting date format into a DATE data type (you can also use the STR_TO_DATE() function to populate a new DATE column from your existing data.) Or you can produce a DATE data type in your php code at the same time you are validating the supplied data values.

 

You can use the msyql DATE_FORMAT() function directly in a query to format a DATA data type as any format you want.