week in Mysql [Re: OT weekdates in MSSQL [Re: just to laugh]]

On 29-8-2020 19:04, Lyle H. Gray wrote:
> Jim H <[email protected]> wrote in
> news:[email protected]:
> 
>>> IIRC iso weeks start on Monday, ordinary weeks start on Sunday.
>>> Look at the linux 'date' command, specifically %V (%G) and %U
>>> That may suggest why 20210101 is isoweek 53.
>>
>> As I (think I) recall... you can get the ISO week by counting
>> Thursdays in the year to date.
> 
> The first "full" week of the year under ISO standard 8601 is the first week
> with four days/first week with a Thursday in it (ISO weeks start on Monday,
> not Sunday, as noted above).
> 
> In this group, though, we should be talking about the functions WEEK(),
> YEARWEEK(), and WEEKOFYEAR(), not DATEPART(), since MySQL doesn't have a
> DATEPART() function.
> 
> 
> 

WITH RECURSIVE days as (
   SELECT cast('2021-01-01' as date) d
   union all
   SELECT date_add(d,INTERVAL 1 day) from days where d < '2021-01-06')
select
    d, week(d),
    week(d,0) W0,
    week(d,1) W1,
    week(d,2) W2,
    week(d,3) W3,
    week(d,4) W4,
    week(d,5) W5,
    week(d,6) W6,
    week(d,7) W7,
    weekofyear(d) WOY,
    yearweek(d) YOW,
    yearweek(d,0) YOW0,
    yearweek(d,1) YOW1
FROM days;

(output, in CSV):
d , week(d) , W0 , W1 , W2 , W3 , W4 , W5 , W6 , W7 , WOY , YOW , YOW0 , YOW1
2021-01-01 , 0 , 0 , 0 , 52 , 53 , 0 , 0 , 53 , 52 , 53 , 202052 , 202052 , 
202053
2021-01-02 , 0 , 0 , 0 , 52 , 53 , 0 , 0 , 53 , 52 , 53 , 202052 , 202052 , 
202053
2021-01-03 , 1 , 1 , 0 , 1 , 53 , 1 , 0 , 1 , 52 , 53 , 202101 , 202101 , 202053
2021-01-04 , 1 , 1 , 1 , 1 , 1 , 1 , 1 , 1 , 1 , 1 , 202101 , 202101 , 202101
2021-01-05 , 1 , 1 , 1 , 1 , 1 , 1 , 1 , 1 , 1 , 1 , 202101 , 202101 , 202101
2021-01-06 , 1 , 1 , 1 , 1 , 1 , 1 , 1 , 1 , 1 , 1 , 202101 , 202101 , 202101

conclusion, there is nothing to laugh about ...