ranjan satpathy ranjan satpathy - 4 months ago 11
SQL Question

MySQL to fetch all the records based on a month of a year

I have a table and in that table there is a column called

date_created
, which is in TIME STAMP format
2016-07-20 11:30:15
and while I am fetching record I have to pass only the month and year in my front-end field like (month_no/year)
1/2016
,
2/2016
,
3/2016
..... So I have to fetch all the record based upon a month. So how could I achieve this query. So far I tried like this.

SELECT t.*
FROM stock t
WHERE MONTH(str_to_date(t.date_created,'%Y/%M')) = 7

Answer

Since you have a timestamp column you don't need to worry too much. You need to put the month number and year number in the corresponding position.

SELECT t.*
FROM stock t
WHERE MONTH(t.date_created) = ?
AND YEAR(t.date_created) = ?;

If month = 7 and year = 2016 then your query:

SELECT t.*
FROM stock t
WHERE MONTH(t.date_created) = 7
AND YEAR(t.date_created) = 2016;