SQL like clause for dates

Question

I am querying a db to get all blog posts from a certain month of a certain year. The date is stored in the database as YYYY-MM-DD

What is the best foolproof method of getting only the posts of a certain day and year? I have tried chaining like clauses, but I've come to realise a few ways in which they would fail, for example if I used the statement:

'SELECT * FROM entries WHERE date LIKE \''.$year.'%\' AND date LIKE \'%'.$month_no.'%\''

This would fail if the month number happened to be contained within the last 3 digits of the year number. Would I have to use regex and if so, could anyone suggest a statement I could use?

Answer 1

Please.. just use a date filter. It will help performance (use an index)

$query = "SELECT *
FROM entries
WHERE date >= '" . $year . "-" . $month_no . "-01'
  AND date <  adddate('" . $year . "-" . $month_no . "-01', interval 1 month)";

That was for MySQL. For SQL Server, use something similar, but use DATEADD instead

...
WHERE date >= '$year" . $month . "01'
  AND date <  DATEADD(month,1,'$year" . $month . "01'";

The reason for adding one month is that it is easier to handle the case when month is 12 (December).