Finding week number in a month in oracle

Question

I have table where I have stored all mondays e.g (1st oct, 8th oct, 15th oct, 22nd oct, 29th oct), I need to find out week of that month which will be like for 1st oct it will 1, 8th oct 2 and henceforth.. for Sept counter should start at 1 again. Can this is done through sql query, database is oracle 10g?

if first on month is coming on wednesday then first monday in that month should be marked as 1, its second week but need as 1.

Solution 1

select to_char(your_date,'W') from dual;

will do it.

A nice table to analyse is here:

Here is a SQL Fiddle to prove it.

Solution 2

A small change is required in the query, check for Jun 01 2014, it shows as first week = 2. Use the below query to check. Same case is applicable for Jan 01 2017

select to_date('06/01/2014','mm/dd/yyyy') curr_day, next_day(trunc(to_date('06/01/2014','mm/dd/yyyy'),'mm')-8, 'sun') prev_week, trunc( (to_date('06/01/2014','mm/dd/yyyy')-next_day(trunc(to_date('06/01/2014','mm/dd/yyyy'),'mm')-8, 'sun'))/7 )+1 "week" from dual;

The correct query should be:

select to_date('06/01/2014','mm/dd/yyyy') curr_day, next_day(trunc(to_date('06/01/2014','mm/dd/yyyy'),'mm')-7, 'sun') prev_week, trunc( (to_date('06/01/2014','mm/dd/yyyy')-next_day(trunc(to_date('06/01/2014','mm/dd/yyyy'),'mm')-7, 'sun'))/7 )+1 "week" from dual;

Solution 3

The source http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:7482139067917

select 
x,
next_day(trunc(x,'mm')-8, 'sun'),
trunc( (x-next_day(trunc(x,'mm')-8, 'sun'))/7 )+1 "week"
from t;

Week started from Monday 'MON'

there is you can check it SQL Fiddle

Comments

[Erich Kitzmueller]

what result do you want when the first of the month is e.g. a wednesday?

[Pravin Satav]

if first on month is coming on wednesday then first monday in that month should be marked as 1, its second week but need as 1. Thanks for pointing this, should have cleared in question itself. cheers

[Kamil Šrot]

You can divide the day of month by 7 - your number is SELECT trunc(DOM/7 + 1) FROM dual...

[Erich Kitzmueller]

What about Saturday, 1st of September, 2012: do you want 0 as result since the 1st Monday in September is the 3rd of September or is it 5 since you assign it to the 5th week of August?

[Pravin Satav]

Sept 2012 data should be like - 3rd sept => 1, 10th sept => 2, 17th sept => 3, 24th sept 4

[Erich Kitzmueller]

I asked specifically about the 1st of September. Or do you only need results for mondays? In that case, Florin's answer will do.

[Pravin Satav]

yes.. florin's answer is what I need.. I have tested the same on my table.. thanks

[Pravin Satav]

small catch here... if first on month is coming on wednesday then first monday in that month should be marked as 1, its second week but need as 1... I have updated the question..

[Florin Ghita]

No problem, to_char(date, 'W') will see any date between 1 and 7 of month as first week.

[Kamil Šrot]

It's not what he needs - your code returns week of YEAR not week of MONTH

[Florin Ghita]

@KamilŠrot you are wrong. Week of year is 'WW'. You can analyse the link I provided.

[Pravin Satav]

Thanks @florin Sept 2012 data should be like - 3rd sept => 1, 10th sept => 2, 17th sept => 3, 24th sept 4 but to_char(date, 'W') would not give me result I m looking for, in table where I m quering only mondays are stored.

[Florin Ghita]

how are stored mondays? because select to_char(to_date('3-Sep-2012'),'W') from dual; gives me 1.

[Pravin Satav]

Thanks.. Its gives me what I need.. I have tested the answer.. :)

[APC]

Florin's answer is correct. I have added a SQL Fiddle to prove it

[APC]

@PravinSatav - so you posted a comment saying it wouldn't work before testing it? Poor show.

[APC]

Well I knocked it up, so somebody might as well get the benefit.

[Cristal Embalagens]

Oracle 11.2 shows Jun 01 2014 and Jan 01 2017 first week = 1.