SQL: Working with only parts of dates

[dogwomble]

Hey guys,

I'm not quite sure about how I'd go about this with a SQL statement, so I thought I'd ask and see if I can come up with a better way.

I'll need to do some tricky reporting with dates, which will require a bit of fancy SQL to get the data I need. Basically, I've got the table set up for something "recurring" weekly, monthly, six-monthly and yearly. These are all stored as Yes/No fields. I've also got a Date field showing the day the data was entered.

What I need to do is write the SQL so that if Weekly recurrance is set, it will grab all the records that were, say, entered on a Thursday, monthly so that all the records that were entered on, say, the 3rd were grabbed, and so on and so on.

What would I need to do with SQL to get this working?

[brucevde]

To get all Weekly records recurring on a Thursday

SELECT *
FROM TableName
WHERE Weekly = 1 AND DATEPART(dw, EntryDAte) = 5

For Monthly records created on the 3rd

SELECT *
FROM TableName
WHERE Monthly = 1 AND DAY(EntryDate) = 3

Syntax may vary depending on your database, I tested the above in SQL Server.

[dogwomble]

Yeah, in this case it's using DAO and Access ... I assume syntax would be more or less the same?