: > : 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 : > : > : : Thank you for the better explanation. I think that you want the following: : The 12 months of data just before the beginning of the next month (all of : this month all of the way back to the "next" month for the previous year, : excluding later dates within the current month. : : As an example: : a) Assume we have a table that contains daily records from 2004-01-01 to : 2005-10-18. : b) You want to run a report based on the date 2005-05-25. : : You have already said that you did not want to see the dates from : 2004-05-26 to 2005-05025. I think what understand that you want to see is : from 2004-06-01 to 2005-05-25. : : Use Michael's formula: : SET @last_month_yr = LAST_DAY(CURDATE()- INTERVAL 1 MONTH) : - INTERVAL 1 YEAR + INTERVAL 1 DAY; : : To compute the earliest date and use the target date as the other end. So : for instance, for the target report date of 2005-05-25, combining : Michael's formula and your query would look like: : : SET @report_target_date = '2005-05-25'; : SET @last_month_yr = LAST_DAY(@report_target_date - INTERVAL 1 MONTH) : - INTERVAL 1 YEAR + INTERVAL 1 DAY; : : SELECT ... : FROM ... : WHERE datefield BETWEEN @last_month_yr AND @report_target_date; : : Does this do what you wanted? :
Hello Folks, It is great help. Thank you all and specially for Michael and Shawn. It does work properly in 4.1.9-max. But I think, LAST_DAY function does not availble in old MySQL version 4.0.??, so there I have problem, actually our webserver is still in that version. So I request any help to alter the query to work in previous version too, and it will be greatly appreciated. Thanks in advance for the answer. Greetings, R.Kumaran -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
