How to do the maths for the dates, to achieve IExplore style history?

[cjard]

IExplore history for today, friday, looks like:

Last Month
3 Weeks ago
2 Weeks Ago
Last Week
Monday
Tuesday
Weds
Thu
Today

Now, oracle is very good, it can tell me what week of the month it is, right now we are in week 4 o0f november, but the problem is that week 4 started this wednesday..

So i cant say:

"If the event occurred this week put the day, else put 'last week')

because for events on monday and tuesday (which were 'last week' in week numbers you get:

last week
last week
weds
thurs
fri


-
So im asking what the funky logic is for working out if a date was this week, last week etc.. in human terms of "week starts on monday" rather than computer terms of "week starts a multiple of 7 days after 1st of month"

Any suggestions?

Hopefully i'll be able to group my events up thus:

This week
+- mon
|    +- event
+- tue
     +- event
Last week
+- event
+- event
N weeks ago (N counts up till month start)
+- event
+- event
October
+- event
+- event
September
+- event
...
2005
+- event
2004
+- event

so the granularity runs:
daily this week
weekly this month
monthly this year
yearly

Any ideas? I can prepare things in oracle cos its got brilliant date handling.. to turn a date into a number:
Day of week, month or year
Week of month or year
Month of year
year
Worded versions of all these

I can implement the logic in SQL or in VB or maybe both.

ANy thoughts appreciated..!



edit: thoughts like:
i can turn a date of the first-of-this-month into a day name, and assign that a value based on the text
Mon = 0, Tue = 1 etc
and this value can be used to assess how much to "roll" a day-of-month number to assess whether it was in a human week

if the month started on a wednesday then the value is 2.. so the week-of-month of (date+2days) is equal to week of month of (today+2)
this means mondays date (20th nov) is rolled forward to 22nd, which is in week 3 -> same week as this week.. it will thus appear that the date is (in human weeks) "this week", whereas the 19th is "last week"

if the this_week = 3 then last weeks dates are any rolled dates in week number (this_week - 1)
else text should be "N weeks ago" where N = this_week_number - rolled_date's_week_number
except for any dates last month (rolled into this month) so i guess the month checking would have to come first, before this logic..

this is the kind of logic im seeking... someone somment on whether this is lucid?