MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:Dobromir Velev Date:October 18 2005 4:39pm
Subject:Re: Fw: query help
View as plain text  
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" <grkumaran@stripped>
> To: <mysql@stripped>
> 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

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