Discussion:
just to laugh
(too old to reply)
Ammammata
2020-08-28 14:38:56 UTC
Permalink
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
2020-08-28 16:05:15 UTC
Permalink
Post by 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
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
2020-08-28 16:17:57 UTC
Permalink
Post by Lyle H. Gray
Post by 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
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.
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>).
Ammammata
2020-08-31 08:38:48 UTC
Permalink
Il giorno Fri 28 Aug 2020 06:17:57p, *Lyle H. Gray* ha inviato su
Post by Lyle H. Gray
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 :/
--
/-\ /\/\ /\/\ /-\ /\/\ /\/\ /-\ T /-\
-=- -=- -=- -=- -=- -=- -=- -=- - -=-
........... [ al lavoro ] ...........
Lyle H. Gray
2020-08-28 16:39:27 UTC
Permalink
Post by Lyle H. Gray
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
2020-08-29 07:05:12 UTC
Permalink
Post by 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
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
2020-08-29 09:07:45 UTC
Permalink
Post by Luuk
Post by 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
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
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
2020-08-29 10:01:30 UTC
Permalink
Post by Chris Elvidge
Post by Luuk
Post by 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
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
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.
$ date -d "2021-01-01" +"%A %G-%V"
Friday 2020-53
$ date -d "2021-01-04" +"%A %G-%V"
Monday 2021-01
Jim H
2020-08-29 16:15:26 UTC
Permalink
Post by Chris Elvidge
Post by Luuk
Post by 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
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
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.
--
Jim H
Lyle H. Gray
2020-08-29 17:04:18 UTC
Permalink
Post by Jim H
Post by 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.
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
2020-08-30 07:51:06 UTC
Permalink
Post by Lyle H. Gray
Post by Jim H
Post by 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.
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 ...
Lyle H. Gray
2020-08-30 15:16:58 UTC
Permalink
Post by 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;
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 ...
Nicely done.
Ammammata
2020-08-31 08:39:17 UTC
Permalink
Il giorno Sat 29 Aug 2020 09:05:12a, *Luuk* ha inviato su
Post by Luuk
select DATEPART(isowk, '20210103')
yes, thank you too :)
--
/-\ /\/\ /\/\ /-\ /\/\ /\/\ /-\ T /-\
-=- -=- -=- -=- -=- -=- -=- -=- - -=-
........... [ al lavoro ] ...........
Jim H
2020-08-29 16:12:28 UTC
Permalink
On Fri, 28 Aug 2020 14:38:56 -0000 (UTC), in
Post by 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
Why is the result above funny?

Hint before answering... the results aren't ISO week dates.
--
Jim H
Ammammata
2020-08-31 08:41:36 UTC
Permalink
Il giorno Sat 29 Aug 2020 06:12:28p, *Jim H* ha inviato su
Post by Jim H
Why is the result above funny?
Hint before answering... the results aren't ISO week dates.
yes, thank you, now I see
--
/-\ /\/\ /\/\ /-\ /\/\ /\/\ /-\ T /-\
-=- -=- -=- -=- -=- -=- -=- -=- - -=-
........... [ al lavoro ] ...........
Loading...