Filter a range of dates with SQL

Question

I have to filter the results of an SQL starting at the current day and displaying all records until the current day + 10 days. So if it's the 22th of December I should display all records starting that day and that aren't after the the 1st of January. How can I do this? I tried a simple query like the one below but it seems to only display the records until the last day of the current month and then instead of showing the records of the next year goes back to the first month of the current year.

SELECT * 
FROM mytable 
WHERE DAY(mydatefield) >= DAY(CURRENT_TIMESTAMP) 
  AND mydatefield <= DATE_ADD(CURRENT_TIMESTAMP, INTERVAL 10 DAY)

EDIT 1

I'm using Symfony 2 + Doctrine 2 querybuilder so the query must be compatible with them

EDIT 2

I solved the Doctrine 2 problem by using the query suggested by eggyal with this PHP code

$queryBuilder->where($queryBuilder->expr()->gte('mydatefield', 'CURRENT_DATE()'))->andWhere($queryBuilder->expr()->lte('mydatefield', 'DATE_ADD(CURRENT_DATE(), 10, \'DAY\')'));

Answer 1

The problem lies in your erroneous use of MySQL's DAY() function, which returns the day of the month. You should use instead DATE(); you can also simplify with the BETWEEN ... AND ... comparison operator:

SELECT * 
FROM   mytable 
WHERE  DATE(mydatefield) BETWEEN CURDATE() AND CURDATE() + INTERVAL 10 DAY

Note that, in order to benefit from index optimisation, you could instead do:

SELECT * 
FROM   mytable 
WHERE  mydatefield >= CURDATE() AND mydatefield < CURDATE() + INTERVAL 11 DAY