just to laugh

4 views
Skip to first unread message

Ammammata

unread,
Aug 28, 2020, 10:38:58 AM8/28/20
to
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

unread,
Aug 28, 2020, 12:05:21 PM8/28/20
to
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

unread,
Aug 28, 2020, 12:18:04 PM8/28/20
to
"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

unread,
Aug 28, 2020, 12:39:34 PM8/28/20
to
"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

unread,
Aug 29, 2020, 3:05:12 AM8/29/20
to
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

unread,
Aug 29, 2020, 5:08:05 AM8/29/20
to
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

unread,
Aug 29, 2020, 6:01:32 AM8/29/20
to
$ 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

unread,
Aug 29, 2020, 1:04:24 PM8/29/20
to
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

unread,
Aug 30, 2020, 3:51:12 AM8/30/20
to
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

unread,
Aug 30, 2020, 11:17:04 AM8/30/20
to
Luuk <lu...@invalid.lan> wrote in
news:5f4b5a6a$0$10258$e4fe...@news.xs4all.nl:
Nicely done.

Ammammata

unread,
Aug 31, 2020, 4:38:50 AM8/31/20
to
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

unread,
Aug 31, 2020, 4:39:20 AM8/31/20
to
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

unread,
Aug 31, 2020, 4:41:37 AM8/31/20
to
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
Reply all
Reply to author
Forward
0 new messages