Week is number of weeks in that month — oracle-tech

    Forum Stats

  • 3,714,815 Users
  • 2,242,633 Discussions


Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Week is number of weeks in that month

ricard888 Member Posts: 173 Bronze Badge
edited December 2020 in SQL & PL/SQL

I am trying to create a new column number of week (wk_no_in_month) in that month like the following output. To get week number is W - week number in a month but due to a new week begins every Saturday and on the first of every month it's not as simple I first thought. As you can see wk_no_in_month there 5 weeks in July and 1st week in August week number wrapped around back to 1 and so on.

The script was a solution carry on from my last post. I started to wrestle with it, but I right now I facing to find week number in a month. Please explain to me where I went wrong.

with test_data as (   

select 2021 as fy 

  from dual        

), fysd as (   

  select to_date(to_char(fy - 1) || '0701', 'yyyymmdd') as fy_start_dte 

  from test_data  

), fy_dates as (   

  select fy_start_dte + level - 1 as dt   

  from  fysd  connect by level <= add_months(fy_start_dte, 12) - fy_start_dte )

  select dt,    

      greatest( trunc(dt + 2, 'iw') - 2, trunc(dt, 'mm') ) as wk_start_dte,    

      least  ( trunc(dt + 2, 'iw') + 4, last_day(dt)  ) as wk_end_dte,    

      dense_rank() over (order by greatest(trunc(dt + 2, 'iw') - 2, trunc(dt, 'mm'))) as period_num,

      to_char(dt + 2,'iw') - to_char(trunc(dt,'mm'),'iw') wk_no_in_month -- I started to wrestle but is wrong solution

  from  fy_dates



Best Answers


  • mathguy
    mathguy Member Posts: 9,463 Gold Crown

    Yes. Even worse - NEXT_DAY doesn't allow a third argument for NLS_DATE_LANGUAGE. This has been discussed in the past, for example here: https://community.oracle.com/tech/developers/discussion/4023654/make-next-day-independent-of-the-invoking-sessions-nls-date-language and submitted as an enhancement request here: https://community.oracle.com/tech/apps-infra/discussion/4393874/add-optional-nls-date-language-parameter-to-next-day

    In any case - to make it entirely independent of NLS settings and without using magic values (a date "known to be" a Saturday), the query can be modified easily as follows. The helper subquery H returns the string SAT, which is the translation of 'Saturday' in the session's current NLS_DATE_LANGUAGE, the same language as will be used for NEXT_DAY.

    with dates (dt) as ( ........ )
    select d.dt, (next_day(d.dt, h.sat) - next_day(trunc(d.dt, 'mm'), h.sat))/7 + 1 as week_no_in_month
    from   dates d cross join (select to_char(trunc(sysdate, 'iw') + 5, 'Day') as sat from dual) h
    order  by dt
Sign In or Register to comment.