2

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
1000111
  • 13,169
  • 2
  • 28
  • 37
Ranjan
  • 929
  • 1
  • 6
  • 19

2 Answers2

2

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;
1000111
  • 13,169
  • 2
  • 28
  • 37
0

If You have created indexes on columns then i would suggest you not use any function in where clause this makes query slow and non-sargable.. Please use the following link why i am saying this

What makes a SQL statement sargable?

I would Suggest you to use the following query.

SELECT t.*
FROM stock t
 WHERE t.date_created between '2016-07-01 00:00:00.000' AND '2016-07-31 00:00:00.000'
HoldOffHunger
  • 18,769
  • 10
  • 104
  • 133
Rohit Gupta
  • 455
  • 4
  • 16
  • I have to pass only month no and year but you are passing complete format. – Ranjan Aug 01 '16 at 05:57
  • If use have indexes on your table then I suggest you not use the month and Year function. Use Can easily Use variable to convert passing month and year and transforming them in complete date time format. Please let me know if you indexes or not. – Rohit Gupta Aug 01 '16 at 06:04