need to find all records with 2009 from a date string of XX/XX/XXXX - Printable Version +- CodeIgniter Forums (https://forum.codeigniter.com) +-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20) +--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23) +--- Thread: need to find all records with 2009 from a date string of XX/XX/XXXX (/showthread.php?tid=21594) Pages:
1
2
|
need to find all records with 2009 from a date string of XX/XX/XXXX - El Forum - 08-14-2009 [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! need to find all records with 2009 from a date string of XX/XX/XXXX - El Forum - 08-14-2009 [eluser]Slowcheetah[/eluser] Maybe something like this? Code: $this->db->like('date', '2009'); need to find all records with 2009 from a date string of XX/XX/XXXX - El Forum - 08-14-2009 [eluser]jpi[/eluser] Have a look at http://dev.mysql.com/doc/refman/5.0/en/regexp.html need to find all records with 2009 from a date string of XX/XX/XXXX - El Forum - 08-14-2009 [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 need to find all records with 2009 from a date string of XX/XX/XXXX - El Forum - 08-14-2009 [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/date-and-time-functions.html need to find all records with 2009 from a date string of XX/XX/XXXX - El Forum - 08-14-2009 [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 "); need to find all records with 2009 from a date string of XX/XX/XXXX - El Forum - 08-14-2009 [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 "); need to find all records with 2009 from a date string of XX/XX/XXXX - El Forum - 08-14-2009 [eluser]Flying Fish[/eluser] sweet, the format will always be the same any benefit to using SUBSTRING over LIKE? need to find all records with 2009 from a date string of XX/XX/XXXX - El Forum - 08-14-2009 [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. need to find all records with 2009 from a date string of XX/XX/XXXX - El Forum - 08-14-2009 [eluser]jpi[/eluser] Quote:Regex is very slow, don’t use it for database queriesTrue 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. |