just to laugh

[Ammammata]

tried this with MS SQL

select DATEPART(wk, '20210101')
select DATEPART(wk, '20210102')
select DATEPART(wk, '20210103')
select DATEPART(wk, '20210104')
select DATEPART(wk, '20210105')
select DATEPART(wk, '20210106')

result

1
1
2
2
2
2

https://en.wikipedia.org/wiki/ISO_8601#Week_dates


--
/-\ /\/\ /\/\ /-\ /\/\ /\/\ /-\ T /-\
-=- -=- -=- -=- -=- -=- -=- -=- - -=-
........... [ al lavoro ] ...........

[Lyle H. Gray]

Ammammata <amma...@tiscalinet.it> wrote in
news:XnsAC27A96038455am...@127.0.0.1:

> tried this with MS SQL
>
> select DATEPART(wk, '20210101')
> select DATEPART(wk, '20210102')
> select DATEPART(wk, '20210103')
> select DATEPART(wk, '20210104')
> select DATEPART(wk, '20210105')
> select DATEPART(wk, '20210106')
>
> result
>
> 1
> 1
> 2
> 2
> 2
> 2
>
> https://en.wikipedia.org/wiki/ISO_8601#Week_dates

And if you use the YEARWEEK() function in MySQL, you get the ISO 8601
standard results (including the year).

You've pointed out that MS SQL Server does not use the ISO 8601 standard
for the definition of the week number.

[Lyle H. Gray]

"Lyle H. Gray" <lyle...@no.spam.gmail.com.invalid> wrote in
news:XnsAC277AF60BBB6gr...@216.166.97.131:

I will also note that you can define the first day of the week using SET
DATEFIRST in MS SQL, which probably explains (at least in part) why it
does not conform to the ISO 8601 standard.

Try your experiment again using DATEPART(isowk,<date>) instead of
DATEPART (wk,<date>).

[Lyle H. Gray]

"Lyle H. Gray" <lyle...@no.spam.gmail.com.invalid> wrote in
news:XnsAC277AF60BBB6gr...@216.166.97.131:

> And if you use the YEARWEEK() function in MySQL, you get the ISO 8601
> standard results (including the year).

I take this back: YEARWEEK() does _not_ appear to use the ISO 8601
standard -- the definition of a "week" appears to be different.

Also, compare with WEEKOFYEAR() results.

[Luuk]

On 28-8-2020 16:38, Ammammata wrote:
> tried this with MS SQL
>
> select DATEPART(wk, '20210101')
> select DATEPART(wk, '20210102')
> select DATEPART(wk, '20210103')
> select DATEPART(wk, '20210104')
> select DATEPART(wk, '20210105')
> select DATEPART(wk, '20210106')
>
> result
>
> 1
> 1
> 2
> 2
> 2
> 2
>
> https://en.wikipedia.org/wiki/ISO_8601#Week_dates
>
>

select DATEPART(isowk, '20210101')
select DATEPART(isowk, '20210102')
select DATEPART(isowk, '20210103')
select DATEPART(isowk, '20210104')
select DATEPART(isowk, '20210105')
select DATEPART(isowk, '20210106')

result

53
53
53
1
1
1

https://docs.microsoft.com/en-us/sql/t-sql/functions/datepart-transact-sql?view=sql-server-ver15

[Chris Elvidge]

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.



--

Chris Elvidge, England

[Luuk]

$ date -d "2021-01-01" +"%A %G-%V"
Friday 2020-53
$ date -d "2021-01-04" +"%A %G-%V"
Monday 2021-01

[Lyle H. Gray]

Jim H <inv...@invalid.invalid> wrote in
news:mlvkkfhv1d4kvorvg...@4ax.com:

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

[Luuk]

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

[Lyle H. Gray]

Luuk <lu...@invalid.lan> wrote in
news:5f4b5a6a$0$10258$e4fe...@news.xs4all.nl:

Nicely done.

[Ammammata]

Il giorno Fri 28 Aug 2020 06:17:57p, *Lyle H. Gray* ha inviato su
comp.databases.mysql il messaggio
news:XnsAC277D1D5E2B7gr...@216.166.97.131. Vediamo cosa ha
scritto:

> Try your experiment again using DATEPART(isowk,<date>) instead of
> DATEPART (wk,<date>).
>
>

Ok, now it's fine, THANK YOU!

Now I'll change all occourrencies :/

[Ammammata]

Il giorno Sat 29 Aug 2020 09:05:12a, *Luuk* ha inviato su
comp.databases.mysql il messaggio
news:5f49fe27$0$10277$e4fe...@news.xs4all.nl. Vediamo cosa ha scritto:

> select DATEPART(isowk, '20210103')
>

yes, thank you too :)

[Ammammata]

Il giorno Sat 29 Aug 2020 06:12:28p, *Jim H* ha inviato su
comp.databases.mysql il messaggio
news:pgvkkfhhtrgm1evu7...@4ax.com. Vediamo cosa ha scritto:

> Why is the result above funny?
>
> Hint before answering... the results aren't ISO week dates.
>

yes, thank you, now I see