1. Forum
  2. Usenet
  3. COMP.DATABASES.MYSQL

  • just to laugh

    From Ammammata@21:1/5 to All on Fri Aug 28 14:38:56 2020
    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 ] ...........

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Lyle H. Gray@21:1/5 to Ammammata on Fri Aug 28 11:05:15 2020
    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.

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Lyle H. Gray@21:1/5 to Lyle H. Gray on Fri Aug 28 11:39:27 2020
    "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.

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Lyle H. Gray@21:1/5 to Lyle H. Gray on Fri Aug 28 11:17:57 2020
    "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>).

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Luuk@21:1/5 to Ammammata on Sat Aug 29 09:05:12 2020
    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

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Chris Elvidge@21:1/5 to Luuk on Sat Aug 29 10:07:45 2020
    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

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Luuk@21:1/5 to Chris Elvidge on Sat Aug 29 12:01:30 2020
    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

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Jim H@21:1/5 to ammammata@tiscalinet.it on Sat Aug 29 16:12:28 2020
    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

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Lyle H. Gray@21:1/5 to Jim H on Sat Aug 29 12:04:18 2020
    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.

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Jim H@21:1/5 to Chris Elvidge on Sat Aug 29 16:15:26 2020
    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

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Ammammata@21:1/5 to All on Mon Aug 31 08:39:17 2020
    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 ] ...........

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Ammammata@21:1/5 to All on Mon Aug 31 08:38:48 2020
    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 ] ...........

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Ammammata@21:1/5 to All on Mon Aug 31 08:41:36 2020
    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 ] ...........

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)