Re: SQL 2020-01-05 is week 2? There is no week 1 in 2020"

[Vernon Hamberg <vhamberg@xxxxxxxxxxxxxxx>]

RTFineM -
> The WEEK function returns an integer between 1 and 54 that represents 
> the week of the year. The week starts with Sunday, and January 1 is 
> always in the first week.

There are different ways of counting weeks, as for different accounting 
methods, IIRC - there's another function, WEEK_ISO, that might give a 
different result, although not in this case, if I read the doc rightly -
> The WEEK_ISO function returns an integer between 1 and 53 that 
> represents the week of the year. The week starts with Monday. Week 1 
> is the first week of the year to contain a Thursday, which is 
> equivalent to the first week containing January 4. Thus, it is 
> possible to have up to 3 days at the beginning of the year appear as 
> the last week of the previous year or to have up to 3 days at the end 
> of a year appear as the first week of the next year.

There's probably an explanation elsewhere, though both are doing 
something to handle partial weeks at start and end of year.

Regards
Vern

On 1/4/2020 1:27 AM, Booth Martin wrote:
> exec sql set :weekNbr = week('2020-01-05');
>
> sets weekNbr at 2?