Subject Re: [firebird-support] Extract week of the year (within year)
Author Kjell Rilbe
svein.erling.tysvaer@... [firebird-support] skrev:
> >is possible to get week of the year "within year"?
> >I see that built in functions only support ISO-8601 (or i do not know
> some parameter)
> Actually, Karol, “week within year” is not the week, Karol. 1 January
> could be week 53 and 31 December could be week 1. If you want to
> consider 1 January always as part of week 1 (even if that day could be
> the only day of week 1), I’d suggest you try something like:
> select (extract(yearday from cast('1.1.2014' as date))-extract(weekday
> from cast('1.1.2014' as date)))/7+1
> from rdb$database
> If you want the first seven days of a week to always be week 1 (and
> changing which day a week start with for each year), you could drop
> the second EXTRACT.

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).