I have a column in a database with a date stored as mm/dd/yyyy. I need to grab all the rows with dates greater than a certain date. (Ex. any date after 01/01/2013). How do I do this using query builder methods?
I have tried:
$this->db->select('DATE_FORMAT(date, '%mm/%dd/%Y') as mydate');
$this->db->where('mydate >','01/01/2013');
$this->db->select("DATE_FORMAT(date, '%m/%d/%Y') as mydate",FALSE);
$this->db->from('table');
$this->db->where("DATE_FORMAT(date,'%Y-%m-%d') > '2013-01-01'",NULL,FALSE);
DATE_FORMAT() does not parse non-standard date formats. Demo of failure: sqlize.online/s/FEGeneral insights:
Avoid using reserved keywords or function names as database table or column names; such conflicts may make your SQL less intuitive or may require wrapping identifiers in dedicated quotes.
Avoid declaring date values as VARCHAR (or any type which is not DATE); such suboptimal type setting will negatively impact query efficiency, limit native functionality, and therefore make queries less intuitive and harder to maintain.
Specific insights:
DATE_FORMAT() is a function which converts a valid date (Y-m-d) into another format. Your date is not in the expected format to be parsed and will unintentionally return NULL.
STR_TO_DATE() is a function which parses a non-standard date string into a standard Y-m-d date.
To make reliable date comparisons, ensure both values are "big-endian"; Y-m-d (10 characters) is most ideal.
CodeIgniter will automatically disable identifier quoting on query builder method parameters which expect identifier values when the value contains (, ), or '.
To return the non-standard date in the same form as in the database, no function call is needed in the SELECT clause.
Omitting the select() method call is equivalent to building the query with SELECT *.
return $this->db
->get_where(
'my_table',
["STR_TO_DATE(date, '%m/%d/%Y') >" => '2013-01-01']
)
->result();