How to calculate the week of the month?

1

Now we can calculate week of year, but we can’t directly calculate week of month. Is there any way?

2

I can use the difference between the current week and the week on the 1st of this month to calculate.

select WEEK(date '2022-5-30') - WEEK(DATE_TRUNC('month',date '2022-5-30')) + 1
3

@bigfacewo Currently there is no direct SQL function, tried a few ways

4

Using the method I proposed above can achieve the same effect.

5

I am just curious why Dremio doesnt calculate week 53 but treats it as week 1 of following year

For last day of this month →

select WEEKOFYEAR( cast( ‘2024-12-31’ as date)), — returns week number of 1
DayOFYEAR( cast( ‘2024-12-31’ as date)) dayofweek_, – returns day number366
cast( ‘2024-12-30’ as date),
date_part(‘week’, cast(‘2024-12-30’ as date))

SQL*Server and other DBs return week 53

6

Welcome to the world of inconsistencies!
MySQL and Postgres both return 1 for 2024-12-30 and 2024-12-31 so ¯_(ツ)_/¯

7

There are multiple ways of calculating week numbers, so it all depends on what system you expect the system to follow.