need to find all records with 2009 from a date string of XX/XX/XXXX

[eluser]Flying Fish[/eluser]
hi all,

I have a database of records which contain a date field, formatted as a string

example: 12/25/2009

I need to return all the records for the year 2009 only, any thoughts on the best way to do that?

I guess I could use a foreach loop in combination with php's string functions, but I was wondering if there is a better way to do it.

Thanks!

[eluser]Slowcheetah[/eluser]
Maybe something like this?

Code:

$this->db->like('date', '2009');

[eluser]jpi[/eluser]
Have a look at http://dev.mysql.com/doc/refman/5.0/en/regexp.html

[eluser]Flying Fish[/eluser]
Here's what I've come up with so far, it seems to work well.

Code:

$query = $this->db->query("SELECT * FROM orders WHERE status = 'complete' AND date_ordered REGEXP '^../../2009$' ORDER BY order_id ASC ");

appreciate the feedback

[eluser]renownedmedia[/eluser]
Regex is very slow, don't use it for database queries. The quick fix would be a LIKE '%2009' but the best method would be to convert the database column from a VARCHAR(10) to a DATETIME field and to use a mysql query based on an EXTRACT YEAR or something.

http://dev.mysql.com/doc/refman/5.1/en/d...tions.html

[eluser]Flying Fish[/eluser]
probably too late to convert to a date field

this seems to work well too, and is a little more precise

Code:

$query = $this->db->query("SELECT * FROM orders WHERE status = 'complete' AND date_ordered LIKE '_____/2009' ORDER BY order_id ASC ");

[eluser]Chad Fulton[/eluser]
You could also do this, since it seems like you know for sure that the format will always be the same:

Code:

$query = $this->db->query("SELECT * FROM orders WHERE status = 'complete' AND SUBSTRING(date_ordered, -4) = '2009' ORDER BY order_id ASC ");

[eluser]Flying Fish[/eluser]
sweet, the format will always be the same

any benefit to using SUBSTRING over LIKE?

[eluser]Chad Fulton[/eluser]
I believe SUBSTRING will be slightly faster, since LIKE is a pattern matching operator rather than a simple string operator.

Unless you're planning on having a lot of users, I'm sure you could use either one.

[eluser]jpi[/eluser]

Regex is very slow, don’t use it for database queries

True

And I would also change varchar for datetime. I don't think it's too late, just write 4-5 lines of php to convert you current rows into the new format.