# How to calculate the week of the month?

**URL:** <https://community.dremio.com/t/how-to-calculate-the-week-of-the-month/9182>\
**Category:** Uncategorized\
**Created:** [May 17, 2022, 10:00am UTC](https://community.dremio.com/t/how-to-calculate-the-week-of-the-month/9182 "2022-05-17T10:00:25Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![bigfacewo](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/bigfacewo/32/4090_2.png) [@bigfacewo](https://community.dremio.com/u/bigfacewo)\
**Post date:** [May 17, 2022, 10:00am UTC](https://community.dremio.com/t/how-to-calculate-the-week-of-the-month/9182/1 "2022-05-17T10:00:25Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![bigfacewo](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/bigfacewo/32/4090_2.png) [@bigfacewo](https://community.dremio.com/u/bigfacewo)\
**Post date:** [May 18, 2022, 1:24am UTC](https://community.dremio.com/t/how-to-calculate-the-week-of-the-month/9182/2 "2022-05-18T01:24:16Z")

</div>

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

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

```

---

<div class="post-metadata">

**Author:** ![balaji.ramaswamy](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/balaji.ramaswamy/32/843_2.png) [@balaji.ramaswamy](https://community.dremio.com/u/balaji.ramaswamy)\
**Post date:** [May 19, 2022, 7:05pm UTC](https://community.dremio.com/t/how-to-calculate-the-week-of-the-month/9182/3 "2022-05-19T19:05:09Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![bigfacewo](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/bigfacewo/32/4090_2.png) [@bigfacewo](https://community.dremio.com/u/bigfacewo)\
**Post date:** [May 20, 2022, 8:02am UTC](https://community.dremio.com/t/how-to-calculate-the-week-of-the-month/9182/4 "2022-05-20T08:02:49Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![raghuj](https://avatars.discourse-cdn.com/v4/letter/r/a88e57/32.png) [@raghuj](https://community.dremio.com/u/raghuj)\
**Post date:** [March 1, 2024, 1:28am UTC](https://community.dremio.com/t/how-to-calculate-the-week-of-the-month/9182/5 "2024-03-01T01:28:24Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![dch](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/dch/32/4140_2.png) [@dch](https://community.dremio.com/u/dch)\
**Post date:** [March 1, 2024, 3:18am UTC](https://community.dremio.com/t/how-to-calculate-the-week-of-the-month/9182/6 "2024-03-01T03:18:41Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Nisden](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/nisden/32/5453_2.png) [@Nisden](https://community.dremio.com/u/Nisden)\
**Post date:** [March 1, 2024, 12:10pm UTC](https://community.dremio.com/t/how-to-calculate-the-week-of-the-month/9182/7 "2024-03-01T12:10:14Z")

</div>

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

> **[Week | Other week numbering systems](https://en.wikipedia.org/wiki/Week#Other_week_numbering_systems)**
>
> In some countries, though, the numbering system is different from the ISO standard. At least six numberings are in use:\[dubious – discuss\]