finding the week number of the current date

[bdee2]

i have a table with a schedule of events. each row consists of a date and time for the event, and a week number based on a tuesday-monday week.

what i woudl liek to do is to take the current date and determine which week number it falls in.

can someone help me out with this? i just cant seem to figure it out.

i am thinking that basically i woudl look at the schedule table and grab the date of the first event. we knwo the first event will be in week 1. then we can look at eh date of that event and determine the beginnign and end of that week.

the trick woudl be, once we have that information, how to count weeks and determint how many weeks from week 1, todays date is.

any advice?

[Michael8]

I would create a Calendar table. This table would contain columns such as: Date, Day, Month, Year, DayOfWeek, DayOfYear, WeekdayFlag, WeekendFlag, WeekofYear, FiscalYear, DayOfFiscalYear, WeekOfFiscalYear, etc.

Insert a few thousand records into this table with the correct values according to your business rules. This table will now hold your business logic regarding dates.

Then, you can query upon this table to find just about anything you need regarding dates. Since you already have information in a table, you can also join to the Calendar table and pull all the information in one swoop.