MySQL Date Calculation Query

ve

Hi, I am going out of my mind with a query I am trying to write which involves date (diff) calculations. Basically I have a table with a DATE column and I want to be able to run a query to select all rows that are in between two different numbers of months away from a specified date.

For example if I had three rows such as

2008-12-21
2008-01-30
2007-05-30

and I specifed a date of 2009-01-01, then the number of months between the specified date and them would be

0
11
19

Then if I specifed a range of 6 - 14 months, then technically only the second row would be returned. I see that I have the function period_diff at my disposal but it takes the date in the format of yyyymm (E.g. 200812) which is different from the format of DATE. I am sure there is a way around this but I can't seem to find it...

Any help genuinely appreciated

ve

I think my last post proved to be one of those cognitive dissonance (sp?) moments, because the date_format function was what I was looking for. Unfortunately I'm not yet out of the woods

So now I've ended up with the following query...

SELECT period_diff(date_format('2009-01-01', '%Y%m'), date_format(Entity.date_field, '%Y%m')) AS mdiff
FROM Entity where mdiff between 6 AND 14 order by mdiff asc

When I run the query likes this...

SELECT period_diff(date_format('2009-01-01', '%Y%m'), date_format(Entity.date_field, '%Y%m')) AS mdiff
FROM Entity order by mdiff asc

it works!!, and gives me all the rows back showing and ordered by the number of months difference between the row date and the date specified. Unfortunately when I go to use the where/between clause it tells me that it doesn't know what mdiff is?? (which is annoying because the order by clause knows what it is).

[edit]
This gives me the rows that are in between the range specified, but won't let me order by the mdiff column
SELECT Entity.date_field
FROM Entity
WHERE period_diff( date_format( '2009-01-01' , '%Y%m' ) , date_format( Entity.date_field, '%Y%m' ) )
BETWEEN 6
AND 14

I know I'm not using an alias in the query above but for the love of god why can't I use a where (filter) based on an alias column name?
[/edit]

ve

Right I got it, hope this is helpful to someone. My final query which works is...

SELECT period_diff(date_format('2009-01-01', '%Y%m'), date_format(Entity.date_field, '%Y%m')) AS mdiff
FROM Entity HAVING mdiff BETWEEN 6 AND 14 ORDER BY mdiff asc

The HAVING clause can see the alias where the WHERE clause couldn't.