CODEWITHSUNDEEP
phphtmlmysqlsql
Anthony
Anthony

Reputation: 51

Selecting month and year for SQL

I currently have a code which selects certain sql queries based on certain information. It is currently as this:

$sql = "SELECT * FROM transaction WHERE id_task = '2' AND id_child = '$number' AND MONTH(datetime) = MONTH('2016-02-13 20:36:41')";

The problem is that while it does in fact select the correct month, I would also like to specify the year, however, I do not know how to do this. Any help would be appreciated

Upvotes: 1

Views: 52

Answers (1)

fthiella
fthiella

Reputation: 49089

A naive solution would be like this:

MONTH(datetime) = MONTH('2016-02-13 20:36:41')
and YEAR(datetime) = YEAR('2016-02-13 20:36:41')

but a better and more performant approach that can make use of an index is this:

WHERE
  datetime>=subdate(date('2016-02-13 20:36:41'), (day('2016-02-13 20:36:41')-1))
  and datetime<last_day('2016-02-13 20:36:41')+interval 1 day

this will calculate the first day of the given month:

subdate(date('2016-02-13 20:36:41'), (day('2016-02-13 20:36:41')-1))

and this will calculate the first day of the next month:

last_day('2016-02-13 20:36:41')+interval 1 day

so you can simply use >=first_day_of_given_month and <first_day_of_next_month

Upvotes: 1

Related Questions