Search code examples
sqloracledate-arithmetic

Finding week number in a month in oracle

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 
  • 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.