Re: Re: [firebird-support] Extract week of the year (within year)
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
RE: [firebird-support] Extract week of the year (within year)
On Tue, 24 Mar 2015 09:23:54 +0100, Svein Erling Tysvær svein.erling.tysv...@kreftregisteret.no [firebird-support] firebird-support@yahoogroups.com wrote: 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: If we are talking about Thursday January 1, 2015, then that is week 1, 2015 according to the ISO definition if **Monday is the first day of the week** in your locale, and it is week 53, 2014 if **Sunday is the first day of the week** in your locale. Last week numbers of the year can range between 51 and 53 iirc. The first week of a year is that week of which the majority of the days (four or more) fall in the new year. For locales using Monday as the first day of the week (most of Europe) that is when the Thursday is in the new year. For locales using Sunday as the first day of the week (eg US) that is when the Wednesday is in the new year. Mark
Re: [firebird-support] Extract week of the year (within year)
=?utf-8?B?U3ZlaW4gRXJsaW5nIFR5c3bDpnI=?=
svein.erling.tysv...@kreftregisteret.no [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).
Kjell
RE: [firebird-support] Extract week of the year (within year)
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.
HTH,
Set
