[EMAIL PROTECTED] wrote:
[EMAIL PROTECTED] wrote on 10/18/2005 12:01:50 PM:
----- 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
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?
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
If I understand correctly, report_target_date is always "today" (the day we
run the query), so I'll simply use CURDATE() or NOW(), as appropriate.
I think the above query will be close, but not quite right, for three reasons:
1) Peter Brawley is right that we should take leap year into account. That
requires changing the order of operations:
SET @start = LAST_DAY(CURDATE()- INTERVAL 1 YEAR)
- INTERVAL 1 MONTH + INTERVAL 1 DAY;
2) If today is any day in May, the report is supposed to start with June 1
of the previous year, not May 1 of the previous year. Hence:
SET @start = LAST_DAY(CURDATE()- INTERVAL 1 YEAR) + INTERVAL 1 DAY;
3) dt is a DATETIME, so we will miss today's entries (unless they were at
exactly midnight) if the end of the range in the WHERE clause is a DATE
instead of a DATETIME.
Try this instead:
SET @start = LAST_DAY(CURDATE()- INTERVAL 1 YEAR) + INTERVAL 1 DAY;
SELECT * FROM t
WHERE dt BETWEEN @start and NOW();
Of course, so long as there are no rows with dt in the future, this
simplifies to
SET @start = LAST_DAY(CURDATE()- INTERVAL 1 YEAR) + INTERVAL 1 DAY;
SELECT * FROM t
WHERE dt >= @start;
Michael
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
