CODEWITHSUNDEEP
sqloracle-databasedateoracle12c
Vicki
Vicki

Reputation: 43

Find the Week from a date passed as input oracle

DB : Oracle Version : 12c

I have to find the week number as on current date based on/from the input Date. Say, my input date is 03/Nov/2019, the query should return week number as 10.

since there are 10 weeks between today date (01/Jan/2020) and input date (03/Nov/2019).

Upvotes: 0

Views: 42

Answers (2)

Zynon Putney II
Zynon Putney II

Reputation: 695

Use the date mask when converting to char:

select to_char(sysdate, 'WW') from dual;

Then you can add or subtract with the following example:

select to_char(to_date('2019-11-03', 'YYYY-MM-DD'), 'WW'), to_char(sysdate, 'WW'),
case 
  when to_char(to_date('2019-11-03', 'YYYY-MM-DD'), 'WW') > to_char(sysdate, 'WW') then
    (52 - to_char(to_date('2019-11-03', 'YYYY-MM-DD'), 'WW')) + to_char(sysdate, 'WW')
  else to_char(sysdate, 'WW') - to_char(to_date('2019-11-03', 'YYYY-MM-DD'), 'WW')
end case
from dual;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271151

How about taking the difference in days and dividing by 7?

select floor((sysdate - date '2019-11-03') / 7) + 1

Upvotes: 5

Related Questions