WEEKOFYEAR() should be removed from the approved function list or it's behavior should be changed

Options
zaclingen_zips
zaclingen_zips Member
in Magic ETL

Having a function to act as a synonym for another function is pretty misleading. Especially when that functionality is different from other implementations of the function.

Make it so WEEKOFYEAR() returns the week number for a given date while assuming the week starts on Monday. Otherwise the function should be deprecated.

Best Answer

  • rco
    rco Contributor
    Answer ✓

    Thanks for bringing this up Zac. We've updated the in-place Magic ETL documentation for the WEEKOFYEAR() function. The new docs will mention the discrepency with MySQL and recommend that WEEK() always be used over WEEKOFYEAR() in the current version of Magic ETL. We can't immediately change the existing behavior of WEEKOFYEAR(), of course, but we're looking into ways we might do it eventually. We don't try to guarantee perfect behavioral parity with MySQL, but we do try to avoid gotchas like this one.

    Randall Oveson <randall.oveson@domo.com>

Answers

  • MarkSnodgrass
    MarkSnodgrass Coach

    Beast mode functions tend to follow the MYSQL definition.
    https://www.w3schools.com/sql/func_mysql_weekofyear.asp

    Where are you see inconsistencies? I got the same result in my card and the w3schools editor when entering in 6/19/2023.

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • zaclingen_zips
    zaclingen_zips Member
    https://community-forums.domo.com/main/discussion/comment/69111#Comment_69111

    WEEKOFYEAR('2023-06-19') should return 25 but WEEKOFYEAR('2023-06-18') also returns 25. WEEKOFYEAR('2023-06-17') returns 24.

    According to that testing, WEEKOFYEAR() assumes the week starts on a Sunday, but the documentation on w3schools that you shared states that the function assumes the week starts on a Monday per MySQL 4.0.

    When you execute 'SELECT WEEKOFYEAR('2023-06-19');' on w3's editor, it returns a 25 but 'SELECT WEEKOFYEAR('2023-06-18');' returns 24.

    Furthermore, the function description on MagicETL's formula editor states that "WEEKOFYEAR() is a synonym for WEEK().". It looks like you can pass in an additional argument for the 'MODE', but that isn't the norm for WEEKOFYEAR().

    Making it so you have to pass an additional argument to get the function to act like it should is misleading. Making it so you have two functions that do the same thing when their behavior is different in other SQL environments is misleading.

  • MarkSnodgrass
    MarkSnodgrass Coach

    I agree with you that it doesn't appear to operating the same as the indicated definition in the formula editor. I would suggest sending it in as a bug to support@domo.com and see if they will fix it. In the meantime, I would use WEEK() as you definitely have more control with the extra parameter options.

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • rco
    rco Contributor
    Answer ✓

    Thanks for bringing this up Zac. We've updated the in-place Magic ETL documentation for the WEEKOFYEAR() function. The new docs will mention the discrepency with MySQL and recommend that WEEK() always be used over WEEKOFYEAR() in the current version of Magic ETL. We can't immediately change the existing behavior of WEEKOFYEAR(), of course, but we're looking into ways we might do it eventually. We don't try to guarantee perfect behavioral parity with MySQL, but we do try to avoid gotchas like this one.

    Randall Oveson <randall.oveson@domo.com>

  • zaclingen_zips
    zaclingen_zips Member
    https://community-forums.domo.com/main/discussion/comment/69261#Comment_69261

    That is awesome! Nice to see that Domo employees watch these threads. Appreciate you hopping on the topic and acting quickly

Categories