CODEWITHSUNDEEP
sqloracle-database
Ashish Sharma
Ashish Sharma

Reputation: 1

Find Occurence of Day with respect to month Oracle

I want to find the occurrence of a particular day with respect to a month using Oracle RDBMS.

For Example: Like if it is 14-Dec-2020 today. So it is 2nd Monday.

So i want a output as 2. Likewise.

More Examples 7-Dec-2020 --> Output Should Be 1 (As it is first Monday of December) 29-Dec-2020 --> Output Should Be 5 (as it is the 5th Tuesday of December)

Upvotes: 0

Views: 148

Answers (3)

Thorsten Kettner
Thorsten Kettner

Reputation: 94884

This is just a little math. The first seven days is the first occurrence for each day, the next seven days is the second occurrrence and so on. So get the day number, e.g. 14, subtract one and apply an integer division then add one again.

select trunc((extract(day from sysdate) - 1) / 7) + 1 from dual;

Upvotes: 0

Mahamoutou
Mahamoutou

Reputation: 1555

  • In the with clause I build the dates I want (e.g. the whole month of December 2020), and I use row_number analytic function to rank my dates based on YYYYMMD format. I use D not DD, because D means the day of the week. I also use nsl_parameter (nls_date_language) because the starting day is not the same for all countries
  • Then I convert the rank column (rnb) to date using to_date(lpad(rnb, 2, '0'), 'DD'). I did that as I need to make oracle spell the occurence using this date format 'DDth'.
  • Then I concatenate the result with "fmDay Month Year" format
with dates as (
select DATE '2020-12-01' + level - 1 dt
  , row_number()over (
      partition by to_char(DATE '2020-12-01' + level - 1, 'YYYYMMD','nls_date_language = ENGLISH') 
      order by DATE '2020-12-01' + level - 1) rnb
from dual
connect by level <= 31
)
select dt
  , Initcap(to_char(to_date(lpad(rnb, 2, '0'), 'DD'), 'DDth','nls_date_language = ENGLISH'))
    ||'  '||to_char(dt, 'fmDay Month YYYY', 'nls_date_language = ENGLISH') Occurence
from dates
order by dt
;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269563

You can use a case expression:

select (case when extract(day from sysdate) <= 7 then '1st '
             when extract(day from sysdate) <= 14 then '2nd '
             when extract(day from sysdate) <= 21 then '3rd '
             when extract(day from sysdate) <= 28 then '4th '
             else '5th '
        end) || to_char(sysdate, 'Day')
from dual;

Here is a db<>fiddle.

Upvotes: 1

Related Questions