Subject Re: Re: [firebird-support] Extract week of the year (within year)
Author liviuslivius
Post date 2015-03-24T10:30:25Z
Thanks all,
 
i forgot about another locales like Sunday first day.
I see that supporting official function in FB will be problematic
 
then i do this in this way
SELECT
 
EXTRACT(YEAR FROM FLD_DATE),
CASE 
    WHEN EXTRACT(yearday FROM FLD_DATE)<100 AND EXTRACT(WEEK FROM FLD_DATE)>50 THEN 0 
    WHEN EXTRACT(yearday FROM FLD_DATE)>100 AND EXTRACT(WEEK FROM FLD_DATE)=1 THEN 100 
    ELSE EXTRACT(WEEK FROM FLD_DATE)    
    END,
SUM(SOMETHING)
GROUP BY 
EXTRACT(YEAR FROM FLD_DATE),
CASE 
    WHEN EXTRACT(yearday FROM FLD_DATE)<100 AND EXTRACT(WEEK FROM FLD_DATE)>50 THEN 0 
    WHEN EXTRACT(yearday FROM FLD_DATE)>100 AND EXTRACT(WEEK FROM FLD_DATE)=1 THEN 100 
    ELSE EXTRACT(WEEK FROM FLD_DATE)    
    END
 
then i got "normal" week numbers but focus week from previous year and week on the next year a week 0 and week 100
then i do not loose order of weeks
 
regards,
Karol Bieniaszewski
 


Also, note that the rules for which week should be week number 1 is
slightly different for different locales. As far as I know, there are
two different rules, but there could be more. (What about locales where
Sunday is considered first day of the week, as opposed to Monday?)

So, id you want to calculate the "official" week number according to a
specific locale's calendar rules, you'll have to look this up
thoroughly. I think Excel's week number function gets it right now
(which hasn't always been the case).

Kjell