1. Forum
  2. USENET
  3. comp.databases.mysql

  • just to laugh

    From Ammammata@ammammata@tiscalinet.it to comp.databases.mysql on Fri Aug 28 14:38:56 2020
    From Newsgroup: comp.databases.mysql

    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 ] ...........
    --- Synchronet 3.21d-Linux NewsLink 1.2
  • From Lyle H. Gray@lylefitzw@no.spam.gmail.com.invalid to comp.databases.mysql on Fri Aug 28 11:05:15 2020
    From Newsgroup: comp.databases.mysql

    Ammammata <ammammata@tiscalinet.it> wrote in news:XnsAC27A96038455ammammatatiscalineti@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.
    --- Synchronet 3.21d-Linux NewsLink 1.2
  • From Lyle H. Gray@lylefitzw@no.spam.gmail.com.invalid to comp.databases.mysql on Fri Aug 28 11:17:57 2020
    From Newsgroup: comp.databases.mysql

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

    Ammammata <ammammata@tiscalinet.it> wrote in news:XnsAC27A96038455ammammatatiscalineti@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.

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

    --- Synchronet 3.21d-Linux NewsLink 1.2
  • From Lyle H. Gray@lylefitzw@no.spam.gmail.com.invalid to comp.databases.mysql on Fri Aug 28 11:39:27 2020
    From Newsgroup: comp.databases.mysql

    "Lyle H. Gray" <lylefitzw@no.spam.gmail.com.invalid> wrote in news:XnsAC277AF60BBB6graynoibisspamcsumas@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.




    --- Synchronet 3.21d-Linux NewsLink 1.2
  • From Luuk@luuk@invalid.lan to comp.databases.mysql on Sat Aug 29 09:05:12 2020
    From Newsgroup: comp.databases.mysql

    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

    --- Synchronet 3.21d-Linux NewsLink 1.2
  • From Chris Elvidge@chris@mshome.net to comp.databases.mysql on Sat Aug 29 10:07:45 2020
    From Newsgroup: comp.databases.mysql

    On 29/08/2020 08:05 am, Luuk wrote:
    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



    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
    --- Synchronet 3.21d-Linux NewsLink 1.2
  • From Luuk@luuk@invalid.lan to comp.databases.mysql on Sat Aug 29 12:01:30 2020
    From Newsgroup: comp.databases.mysql

    On 29-8-2020 11:07, Chris Elvidge wrote:
    On 29/08/2020 08:05 am, Luuk wrote:
    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



    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



    --- Synchronet 3.21d-Linux NewsLink 1.2
  • From Jim H@invalid@invalid.invalid to comp.databases.mysql on Sat Aug 29 16:12:28 2020
    From Newsgroup: comp.databases.mysql

    On Fri, 28 Aug 2020 14:38:56 -0000 (UTC), in <XnsAC27A96038455ammammatatiscalineti@127.0.0.1>, Ammammata <ammammata@tiscalinet.it> 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

    Why is the result above funny?

    Hint before answering... the results aren't ISO week dates.
    --
    Jim H
    --- Synchronet 3.21d-Linux NewsLink 1.2
  • From Jim H@invalid@invalid.invalid to comp.databases.mysql on Sat Aug 29 16:15:26 2020
    From Newsgroup: comp.databases.mysql

    On Sat, 29 Aug 2020 10:07:45 +0100, in <rid5ti$drk$1@dont-email.me>,
    Chris Elvidge <chris@mshome.net> wrote:

    On 29/08/2020 08:05 am, Luuk wrote:
    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



    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
    --- Synchronet 3.21d-Linux NewsLink 1.2
  • From Lyle H. Gray@lylefitzw@no.spam.gmail.com.invalid to comp.databases.mysql on Sat Aug 29 12:04:18 2020
    From Newsgroup: comp.databases.mysql

    Jim H <invalid@invalid.invalid> wrote in news:mlvkkfhv1d4kvorvga6jhoj959pp44j3uu@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.



    --- Synchronet 3.21d-Linux NewsLink 1.2
  • From Luuk@luuk@invalid.lan to comp.databases.mysql on Sun Aug 30 09:51:06 2020
    From Newsgroup: comp.databases.mysql

    On 29-8-2020 19:04, Lyle H. Gray wrote:
    Jim H <invalid@invalid.invalid> wrote in news:mlvkkfhv1d4kvorvga6jhoj959pp44j3uu@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.




    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 ...
    --- Synchronet 3.21d-Linux NewsLink 1.2
  • From Lyle H. Gray@lylefitzw@no.spam.gmail.com.invalid to comp.databases.mysql on Sun Aug 30 10:16:58 2020
    From Newsgroup: comp.databases.mysql

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

    On 29-8-2020 19:04, Lyle H. Gray wrote:

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

    Nicely done.
    --- Synchronet 3.21d-Linux NewsLink 1.2
  • From Ammammata@ammammata@tiscalinet.it to comp.databases.mysql on Mon Aug 31 08:38:48 2020
    From Newsgroup: comp.databases.mysql

    Il giorno Fri 28 Aug 2020 06:17:57p, *Lyle H. Gray* ha inviato su comp.databases.mysql il messaggio news:XnsAC277D1D5E2B7graynoibisspamcsumas@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 :/
    --
    /-\ /\/\ /\/\ /-\ /\/\ /\/\ /-\ T /-\
    -=- -=- -=- -=- -=- -=- -=- -=- - -=-
    ........... [ al lavoro ] ...........
    --- Synchronet 3.21d-Linux NewsLink 1.2
  • From Ammammata@ammammata@tiscalinet.it to comp.databases.mysql on Mon Aug 31 08:39:17 2020
    From Newsgroup: comp.databases.mysql

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

    select DATEPART(isowk, '20210103')


    yes, thank you too :)
    --
    /-\ /\/\ /\/\ /-\ /\/\ /\/\ /-\ T /-\
    -=- -=- -=- -=- -=- -=- -=- -=- - -=-
    ........... [ al lavoro ] ...........
    --- Synchronet 3.21d-Linux NewsLink 1.2
  • From Ammammata@ammammata@tiscalinet.it to comp.databases.mysql on Mon Aug 31 08:41:36 2020
    From Newsgroup: comp.databases.mysql

    Il giorno Sat 29 Aug 2020 06:12:28p, *Jim H* ha inviato su comp.databases.mysql il messaggio news:pgvkkfhhtrgm1evu71v6jq08iqtnhm8sum@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
    --
    /-\ /\/\ /\/\ /-\ /\/\ /\/\ /-\ T /-\
    -=- -=- -=- -=- -=- -=- -=- -=- - -=-
    ........... [ al lavoro ] ...........
    --- Synchronet 3.21d-Linux NewsLink 1.2