Hi,
The following query will probably work but I think it will be easier to pass
the minimum date from your application.
SELECT * FROM t WHERE (year(dt)=year(Now()) and dt<Now()) or
(year(dt)=year(Now())-1 and month(dt)>month(Now()))
Also you might want to check the other Date and Time functions
http://dev.mysql.com/doc/refman/4.1/en/date-and-time-functions.html
I'm sure there is a better solution then the above, and the query will be much
more optimized if you can send the minimum date from your application like
this.
SELECT * FROM t WHERE dt>$date;
Shawn's idea is also good - I just saw his response using the LAST_DAY()
function.
--
Dobromir Velev
On Tuesday 18 October 2005 19:01, grKumaran wrote:
> ----- Original Message -----
> From: "grKumaran" <grkumaran@stripped>
> To: <mysql@stripped>
> Sent: Tuesday, October 18, 2005 16:08
> Subject: query help
>
> : Hello,
> :
> : CREATE TABLE t (
> : dt datetime
> : )
> :
> : Please assume this is the table structure and contains thousands of
>
> records.
>
> : And I want to list them only last 12 months (that mean last 1 year)
>
> records
>
> : exactly.
> :
> : For that I tried using the following query, but it list sometimes 13
>
> months
>
> : when the current date is in the middle of the month.
> :
> : SELECT * FROM t WHERE DATE_ADD(dt, INTERVAL 1 YEAR) >= NOW();
> :
> : I request you to help me. And thanking you for the consideration.
>
> Thanking you all people. I think I am not clear in last mail, here I go in
> more detail.
>
> Sample records:
> 2004-05-25
> 2004-06-25
> 2004-07-25
> 2004-08-25
> 2004-09-25
> 2004-10-25
> 2004-11-25
> 2004-12-25
> 2005-01-25
> 2005-02-25
> 2005-03-25
> 2005-04-25
> 2005-05-25
>
> Let us assume we are in any date of May month, then I want the records
> starts from June 01 to the current datetime.
>
> Required query should bring the following result
> -- if we are on 25th day or later of May month
> 2004-06-25
> 2004-07-25
> 2004-08-25
> 2004-09-25
> 2004-10-25
> 2004-11-25
> 2004-12-25
> 2005-01-25
> 2005-02-25
> 2005-03-25
> 2005-04-25
> 2005-05-25
>
> -- if we are before 25th day of May.
> 2004-06-25
> 2004-07-25
> 2004-08-25
> 2004-09-25
> 2004-10-25
> 2004-11-25
> 2004-12-25
> 2005-01-25
> 2005-02-25
> 2005-03-25
> 2005-04-25
>
> Once again thanking you all the people. And forgive me for any mistakes in
> my English.
>
> Sincerely,
> R. Kumaran
