[EMAIL PROTECTED] wrote on 10/18/2005 06:28:03 AM: > 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. > > Sincerely, > R.Kumaran > >
Assume for a moment that 1 CALENDAR YEAR = 12 months = 365 DAYS = 8760 HOURS = 525600 MINUTES = 31536000 seconds (ignoring leap years) The following calculation is correct: mysql>SELECT NOW(), NOW() - INTERVAL 1 YEAR; +---------------------+-------------------------+ | NOW() | NOW() - INTERVAL 1 YEAR | +---------------------+-------------------------+ | 2005-10-18 09:38:07 | 2004-10-18 09:38:07 | +---------------------+-------------------------+ 1 row in set (0.09 sec) That is exactly 12 months ago (to the second). I don't understand the problem. What kind of date-range calculation would you like to have? If you do not want to go back one whole year, how far do you want to go back? Shawn Green Database Administrator Unimin Corporation - Spruce Pine
