0

Evrey row in my table has a date Y-m-d. Now I want to select only those rows that have a date from before a certain month and year independet of the day. So for example I want all rows with a date befor january 2014.

How would I do this in MySQL? Only considering the month does not work, because it will give me also rows with a month smaller, but a year after my year. E.g. i want rows with date smaller than 03/2013. It will give me also 02/2014.

Of course I could in php define first the first day of the month and than compare the full date. But this does no seem too great and when I want to compare dates after a certain month I have to know how many days a month has.

almo
  • 6,107
  • 6
  • 43
  • 86
  • Just regarding `But this does no seem too great and when I want to compare dates after a certain month I have to know how many days a month has` see this, it may help you http://uk3.php.net/cal_days_in_month. Maybe you could use timestamps to compare dates, look at this for the query http://stackoverflow.com/questions/11133760/mysql-convert-datetime-to-unix-timestamp – TMH Feb 27 '14 at 16:03
  • why not just set the filter as the first day and use a less than operator? As for after, just take the first day of the next month and use a greater than or equal to operator. – Reuben L. Feb 27 '14 at 16:06

1 Answers1

1

You can use the STR_TO_DATE function to convert to actual DATE type and then do a simple comparison to the cut-off date.

 SELECT *
   FROM Your_Table
  WHERE STR_TO_DATE(Your_Date_Column,'%Y-%m-%d') < STR_TO_DATE('2013-03-01','%Y-%m-%d');
Mike Dinescu
  • 54,171
  • 16
  • 118
  • 151