Thanks for the reply.

For info, whilst working my way through the myriad of migration issues I’m having I have been reading the Oracle docs, so much in fact that it’s not uncommon for my eyes to be bleeding by the end of the day.
It's just nice to get some helpful advice every once in a while to prove to yourself that the world isn't such a bad place...

Saying that though I am now going to unashamedly ask for some more help – and I admit that I have only spent a hour or so looking into this, this very morning.

My solution to this problem can be solved with the below SQL (vendortest.vt_datetime is a TIMESTAMP column)

SELECT
to_char(vt_datetime, 'Day') AS day,
to_char(vt_datetime, 'Month') AS monthname,
to_char(vt_datetime, 'MM') AS month,
case when to_char(vt_datetime, 'D') = '7'
then
to_char(vt_datetime + 1, 'iw')
else
to_char(vt_datetime, 'iw')
end as week,
to_char(vt_datetime, 'yyyy') AS year,
to_char(vt_datetime, 'q') AS quarter
from vendortest;

And so for the dates:
24-OCT-09 00.00.00.000000000 (Sat)
25-OCT-09 00.00.00.000000000 (Sun)
26-OCT-09 00.00.00.000000000 (Mon)
I get :

DAY MONTHNAME MONTH WEEK YEAR QUARTER
--------- --------- ----- ---- ---- -------
Saturday October 10 43 2009 4
Sunday October 10 44 2009 4
Monday October 10 44 2009 4

What I was thinking though is that it would be nice to have a custom SQL BIF to do this.

Something along the lines of: (Note this doesn’t work and the syntax is probably a load of b****cks)

create or replace function
year_week_number(p_date)
return TIMESTAMP DETERMINISTIC
AS
correct_week TIMESTAMP
BEGIN
correct_week :=
select case when to_char(vt_datetime, 'D') = '7'
then
to_char(vt_datetime + 1, 'iw')
else
to_char(vt_datetime, 'iw')
end from dual;
return correct_week;
END
END;

I could then call this along the lines of:
select year_week_number(to_date('25-OCT-2009')) from dual

What do you think?