Hi,

I am trying to figure out a way to build a query that shows me results of the past six months.

I tried:

select * from MyTable where date between CURRENT_DATE-6 and CURRENT_DATE

But this only shows the last 6 days.

Is there a (simple) way to achieve the above in months?



Kind regards,

Robert Kloeg

rkloeg


It depends on how accurate you want to be. CURRENT_DATE is working much the same as a TDate in Delphi so its in days from a point. If you're happy with an approximation of 6 months then simply subtract 182 (or 183). If you want it strictly correct (eg 08/03/2011 to 08/09/2011) then its more difficult.

You can use the EXTRACT function to get the year, month and day. That bits easy, however, allowing for crossing a year boundary, or 30 vs 31 vs 28 vs 29 (don't forget leap years) days isn't.

I'm sure John Hay can do it but personally I'd cheat and set up a user defined function - easier on my poor old brain

Roy Lambert [Team Elevate]

Thanks for thinking with me Roy,

I couldn't wrap my head arround the crossing year boundary.
I figured out a different approach though... Incmonth:

date := DateToStr(IncMonth(TDate(now),-6)
Q.SQL.Text := 'select * from MyTable where datum between "'+date+'" and CURRENT_DATE';

This seems to work just fine.

Robert Kloeg

rkloeg


For some reason I totally forgot about using parameters and hence doing some of it in Delphi

Roy Lambert

<rkloeg> wrote in message
news:A8C52492-E2E6-4682-BA0D-F4B3EA21F22C@news.elevatesoft.com...
> Thanks for thinking with me Roy,
>
> I couldn't wrap my head arround the crossing year boundary.
> I figured out a different approach though... Incmonth:
>
> date := DateToStr(IncMonth(TDate(now),-6)
> Q.SQL.Text := 'select * from MyTable where datum between "'+date+'" and
> CURRENT_DATE';
>
> This seems to work just fine.

Are you sure? DateToStr should not normally return an SQL date YYYY-MM-DD.

Robert K

Hi everyone

> date := DateToStr(IncMonth(TDate(now),-6)
> Q.SQL.Text := 'select * from MyTable where datum between "'+date+'" and
> CURRENT_DATE';

The corrrect way is:

var dat: String;
begin
       date := formatdatetime('yyyy-mm-dd',IncMonth(TDate(now),-6));
       Q.SQL.Text := 'select * from MyTable where datum between "'+date+'"
and CURRENT_DATE';

Eduardo