Access SQL date functions 1

[emozley]

Is it possible to pull all records from a table called Users where a column called DateStarted meets the following criteria?

1. DateStarted must be at least 5 years ago

2. The anniversary of DateStarted must be within the next 30 days from the current date.

I can do it in ASP using DateDiff function but not in plain SQL.

Thanks very much

Ed

 

[emozley]

So far I've got

SELECT *
FROM Users
WHERE DATEDIFF("YYYY", DateStarted, DATE())>4

just not sure how what to put in next to then find where the day and month of DateStarted are within 30 days of Date() for any given year - eg the anniversary.

cheers

Ed

 

[Golom]

Try

SELECT *
FROM Users
WHERE DATEDIFF("Y", DateStarted, DATE())>4
AND DateDiff ("d", Date(), 
DateSerial (Year(Date()), Month(DateStarted), Day(DateStarted))) >0 
AND DateDiff ("d", Date(), 
DateSerial (Year(Date()), Month(DateStarted), Day(DateStarted))) < 31)

 

[emozley]

Fantastic thanks very much.

cheers

Ed