MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:grKumaran Date:October 18 2005 5:50pm
Subject:Re: Fw: query help
View as plain text  
: > : 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

Thread
Fw: query helpgrKumaran18 Oct
  • Re: Fw: query helpSGreen18 Oct
  • Re: Fw: query helpDobromir Velev18 Oct
    • Re: Fw: query helpMichael Stassen18 Oct
Re: Fw: query helpgrKumaran18 Oct
  • Re: Fw: query helpMichael Stassen18 Oct
Re: Fw: query helpMichael Stassen18 Oct