What would be wrong with:
SELECT date_effective FROM your_table WHERE date_effective <= (now() + '3 years ago')
Certainly looks alittle mmore readable.
Andy
On Thu, 13 Apr 2000, Moray McConnachie wrote:
> WHERE age('now',date_effective::datetime)<='3 years'::timespan
>
> I don't know if 'now' in that context is still the authoritative way to
> write now, maybe it should be
>
> WHERE age(now(),date_effective::datetime)<='3 years'::timespan
>
> if date_effective is actually a datetime field, which I understand we are
> moving to in 7.0 (date fields being non-SQL-standard), then you don't need
> the cast, obviously.
> ----------------------------------------------------------------
> Moray.McConnachie@computing-services.oxford.ac.uk
>
> ----- Original Message -----
> From: "Andrew Perrin - Demography" <aperrin@demog.berkeley.edu>
> To: <pgsql-sql@postgresql.org>
> Sent: Wednesday, April 12, 2000 6:46 PM
> Subject: [SQL] Subtracting from a date
>
>
> > I need to find all records with a date field (date_effective) 3 years or
> > less prior to now(). This clearly must be possible, but I'm not figuring
> > it out. Any suggestions?
> >
> > ---------------------------------------------------------------------
> > Andrew J. Perrin - aperrin@demog.berkeley.edu - NT/Unix Admin/Support
> > Department of Demography - University of California at Berkeley
> > 2232 Piedmont Avenue #2120 - Berkeley, California, 94720-2120 USA
> > http://demog.berkeley.edu/~aperrin --------------------------SEIU1199
> >
> >
>