My Sql Query to get All previous records from Selected Month And year

Question

I want to get all previous records from my mysql table from selected month and year. e.g if my selected month and year is 08-2018 then query should show all the previous records from Aug 2018.

I have tried this mysql query as :

"select * form tblusers where year(created_date)<=2018 and month(created_date)<'08'";

but it did not include record from when I have month greater than 8 e.g 09-2017 etc

score 1 · Accepted Answer · answered Aug 13 '18 at 12:42

1

You can try simpler way :

Getting records created_date is lesser then 1st date of selected month and year.

in case your created_date field is of type Date then you can try following:

"select * form tblusers where created_date < '2018-08-01'"

in case your created_date field is of type Datetime then you can try following:

"select * form tblusers where created_date < '2018-08-01 00:00:00'"

answered Aug 13 '18 at 12:42

Yogesh Salvi

1,177
2
10
17

1

Both queries above will return the same result created_date being date or datetime. MySQL will do implicit conversion to correct datatype. And yes, this is by far the easiest way. – slaakso Aug 13 '18 at 13:48

Pradeep · Answer 2 · 2018-08-13T15:11:09.983

1

Hope this will help you :

Use CI query builder class like this :

$year = '2018';
$month = '08';
$query = $this->db->from('tblusers')
         ->where('MONTH(created_date) <', $month)
         ->where('YEAR(created_date) <=', $year)
         ->get();
if ($query->num_rows() > 0) 
{
  print_r($query->result());
}

echo $this->db->last_query();

Better way to use like this :

$date = '2017-09-01';
$query = $this->db->from('tblusers')
         ->where('DATE(created_date) >=', $date)
         ->get();

For more : https://codeigniter.com/user_guide/database/query_builder.html

edited Aug 13 '18 at 15:11

answered Aug 13 '18 at 12:51

Pradeep

9,667
13
27
34

sir,if my answer helps you pls don't hesitate to check it as green and do upvote also – Pradeep Aug 14 '18 at 15:23

score 0 · Answer 3 · answered Aug 13 '18 at 12:36

0

Try this:

select * form tblusers where  month(created_date)<8 and year(created_date)<=2018

answered Aug 13 '18 at 12:36

Fahmi

37,315
5
22
31

score 0 · Answer 4 · answered Aug 13 '18 at 12:38

SELECT * FROM tblusers WHERE YEAR(created_date)<='2018' AND MONTH(created_date)<='08';

Try this to equal the month as well + you have an error in your sql syntax in the word "from" (but i guess it's not the error as long as you get results.

Your query works right based on what you have set there. It should not return results higher that 08 month, that's actually your condition:

month(created_date)<'08'

that means that if your value is 09,10,11 ... it does not pass your condition as it is higher than 08.

score 0 · Answer 5 · answered Aug 13 '18 at 12:40

0

I prefer to create a compound value

select * from tblusers where 
(year(created_date) * 12 +  month(created_date))
<= 2018 * 12 + 8;

answered Aug 13 '18 at 12:40

P.Salmon

17,104
2
12
19

My Sql Query to get All previous records from Selected Month And year

5 Answers5