PHP and MySQL syntax: Difference between revisions

mysql ex: find the records in January, {{CURRENTYEAR}}<ref>[http://stackoverflow.com/questions/508791/mysql-query-group-by-day-month-year sql - MySQL Query GROUP BY day / month / year - Stack Overflow]</ref> Data type of column `time` is {{kbd | key = datetime}} (ex: yy:mm:dd hh:mm:ss). Using one of sql queries as following. [http://ideone.com/kJEgnV online demo]

* SELECT * FROM `table_name` WHERE YEAR(`time`) = '{{CURRENTYEAR}}' AND MONTH(`time`) = '1'    /*MONTH(`time`) = '1' or MONTH(`time`) = '01' are both ok*/

* SELECT * FROM `table_name` WHERE DATE_FORMAT(`time`, '%Y-%m') = '{{CURRENTYEAR}}-01'

* SELECT * FROM `table_name` WHERE `time` LIKE '{{CURRENTYEAR}}-01%' /*find the time records starting with the string 2014-01 */

* SELECT * FROM `table_name` WHERE DATE(`time`) between '{{CURRENTYEAR}}-01-01' AND '{{CURRENTYEAR}}-01-31' {{exclaim}} add the DATE function

* SELECT * FROM `table_name` WHERE `time` >= '{{CURRENTYEAR}}-01-01 00:00:00' AND `time` < '{{CURRENTYEAR}}-02-01 00:00:00'

* SELECT * FROM `table_name` WHERE `time` >= '{{CURRENTYEAR}}-01-01 00:00:00' AND `time` <= '{{CURRENTYEAR}}-01-31 59:59:59'