I am faily new to SQL and I am trying to construct a query that returns all records with a specified date field is 1 year old or more.

I have been rummaging through all the online SQL references, tutorials, etc for the last frew hours and all I have found is CURRENT_DATE which returns a DATE value for the current system date. I have also found refernences to the INTERVAL function, which I believe can specify date or time intervals.

I think perhaps that these two applied together could perhaps provide an expression I could use in a where clause. but I cannot figure out the syntax, or even if I am on the right track.

Basically what I want to accomplish is

SELECT * FROM TABLE
WHERE TABLE.DATEFIELD < CURRENT_DATE-(1 YEAR)

But I do not know how to do this.

Any help is greatly appreciated

Thanks,

Eric

Did you try:

I could try that. But how would it know I meant 365 days and not 365 minutes or hours for that matter.

Actually, I was able to accomplish what I wanted with the following

This works for today. But what if I wanted to run this everyday without having to manually change the query? There has to be a way to automate this ...

How would you know? Execute
SELECT datefield, datefield - 365 last_year
FROM table;
and you'll see the result.

Oracle interprets number constants in arithmetic date expressions as number of days. For example, SYSDATE + 1 is tomorrow. SYSDATE + (10/1440) is ten minutes from now.

However, not all years have 365 days (Feb 29th, right?).

There's a function named ADD_MONTHS in Oracle. Your example would then be
SELECT * FROM table
WHERE datefield < ADD_MONTHS(sysdate, -12);

Is there something similar in SQL version you use?

if you are using microsoft sql server

SELECT * FROM TABLE
WHERE TABLE.DATEFIELD < dateadd(yyyy,-1,getdate())

if you are using microsoft access

SELECT * FROM TABLE
WHERE TABLE.DATEFIELD < dateadd("yyyy",-1,date())

Sorry guys, I am using Firebird 1.0.3. The only function mentioned that recently became available in Firebird 1.5 is ADD_MONTHS, which, of course, I cannot use until I upgrade. (Not a viable option at thiis time)

However a special thanks to LKBrwn_DBA (and littlefoot with your follow up) since the syntax:

CURRENT_DATE-365 did indeed work.

Sometime the simplest solution is the best

Thanks!

Eric