Conference orarep::nomahs::rdb_60

We didn't implement such a function because the definition is different in
different parts of the world.  e.g. some counties say that the week starts on
a Sunday, and some say Monday.  Thus for different years the result will be
different.

It should be easy to write a stored function which uses EXTRACT(weekday) and
EXTRACT(julian) with date arithmetic to calculate the correct value.

Ian

I forget why, but this extract is from some stuff i was playing with a year
plus back.  I forget the logic behind it.

case
(EXTRACT
 (WEEKDAY FROM
  (
   cast( !! get 1st day of year for this date
    cast(datevms as char(4))||'01010000000'
        as date vms)

  )
 )
)
!!
!! TO WORK AROUND INTEGER DIVISION RESULTS, ADD TO THE JULIAN DATE
!! SO EACH WEEK STARTS ON SUNDAY, AND ALL WEEKS FOR ANY YEAR START AT
!! WEEK NUMBER 1.
!!
!!
when 1 then cast((extract(julian from datevms)+4)/7 as integer)
when 2 then cast((extract(julian from datevms)+5)/7 as integer)
when 3 then cast((extract(julian from datevms)+6)/7 as integer)
when 4 then cast((extract(julian from datevms)+7)/7 as integer)
when 5 then cast((extract(julian from datevms)+8)/7 as integer)
when 6 then cast((extract(julian from datevms)+9)/7 as integer)
else cast((extract(julian from datevms)+3)/7 as integer)
end as weekno

Sorry but the previous reply is not correct (due to floating point rounding).

This module for Rdb7 should suffice.  For V6.0/V6.1 you could change the code
to be stored procedures.

enjoy,

Ian

set dialect 'SQL92';
attach 'file db$:scratch';

create module date_time_calc
    language SQL

    function TRUNC (in :val real)
    returns integer
    comment is  'Truncates the fractional portion of a floating value '
    /           'and returns the integer portion '                     
    /           'Note: there should be a more efficient way to do this';
    begin
    -- convert float to fixed point string
    declare :tv varchar(16) =
        CAST(CAST(:val as integer(1)) as varchar(16));
    -- locate the decimal point
    declare :p integer =
        POSITION('.' in :tv from 1);
    -- convert the integer portion to binary and return
    return CAST(SUBSTRING(:tv from 1 for :p-1) as integer);
    end;

    function WEEKNUMBER (in :dt date)
    returns integer
    comment is  'This function calculates the week number for the '
    /           'supplied date.  Note that there is no accepted '
    /           'standard for this and this routine will need '
    /           'to be changed for your national conventions. '
    /           'The convention here is that Sunday is the first '
    /           'day of the week.';
    begin
    -- need the first day of the year
    declare :ys date =
        CAST(CAST(EXTRACT(YEAR from :dt) as char(4)) || '-1-1' as date);
    -- need the julian day for the provided date
    declare :jl integer =
        EXTRACT(JULIAN from :dt);
    -- need offset from beginning of the week (Sunday)
    -- extract(weekday) returns Mon=1 and Sun=7
    -- set the variable to Mon=0 to Sat=5, Sun=-1
    declare :wd integer = case EXTRACT(WEEKDAY from :ys)
                            when 7 then -1      -- Sun
                            else EXTRACT(WEEKDAY from :ys) - 1
                          end;
    return 1 + TRUNC((:jl + :wd) / 7);
    end;
end module;

-- 1997
select WEEKNUMBER (date'1997-1-1') from rdb$database;
select WEEKNUMBER (date'1997-1-2') from rdb$database;
select WEEKNUMBER (date'1997-1-3') from rdb$database;
select WEEKNUMBER (date'1997-1-4') from rdb$database;
select WEEKNUMBER (date'1997-1-5') from rdb$database;
select WEEKNUMBER (date'1997-1-6') from rdb$database;
select WEEKNUMBER (date'1997-1-7') from rdb$database;
select WEEKNUMBER (date'1997-1-8') from rdb$database;
select WEEKNUMBER (date'1997-1-9') from rdb$database;
select WEEKNUMBER (date'1997-1-10') from rdb$database;
select WEEKNUMBER (date'1997-1-11') from rdb$database;
select WEEKNUMBER (date'1997-1-12') from rdb$database;
select WEEKNUMBER (date'1997-1-31') from rdb$database;
select WEEKNUMBER (date'1997-2-1') from rdb$database;

>Sorry but the previous reply is not correct (due to floating point rounding).

did you try it?? i believe that what the + 4, 5, 6, etc is to get around.

~did you try it?? i believe that what the + 4, 5, 6, etc is to get around.

Actually I didn't.  I read the comment and it sounded like the + 4, 5, 6 etc
were to make the days align with Sunday.  I apologise if you have something
that works...

Ian