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" <[EMAIL PROTECTED]> > To: <mysql@lists.mysql.com> > 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
