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

zaclingen_zips

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.

rco

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.

MarkSnodgrass

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.

zaclingen_zips

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

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.

rco

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.

zaclingen_zips

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