Oracle Sql Query to Find Out Number Of Weeks Between Two Dates

In this post I would like to give a query to find out the number of weeks between two dates. 

Query

select  abs(to_number(to_char(to_date('&FromDT_dd_mon_yyyy','dd-mon-yyyy'), 'WW'))
  2* - to_number(to_char(to_date('&ToDT_dd_mon_yyyy','dd-mon-yyyy'),'WW')) ) "Number of weeks" 

from dual

 

SQL> select  abs(to_number(to_char(to_date('&FromDT_dd_mon_yyyy','dd-mon-yyyy'), 'WW'))
- to_number(to_char(to_date('&ToDT_dd_mon_yyyy','dd-mon-yyyy'),'WW')) ) "Number of weeks" from dual ;  2
Enter value for fromdt_dd_mon_yyyy: 01-Jan-2017
old   1: select  abs(to_number(to_char(to_date('&FromDT_dd_mon_yyyy','dd-mon-yyyy'), 'WW'))
new   1: select  abs(to_number(to_char(to_date('01-Jan-2017','dd-mon-yyyy'), 'WW'))
Enter value for todt_dd_mon_yyyy: 31-Jan-2017
old   2: - to_number(to_char(to_date('&ToDT_dd_mon_yyyy','dd-mon-yyyy'),'WW')) ) "Number of weeks" from dual
new   2: - to_number(to_char(to_date('31-Jan-2017','dd-mon-yyyy'),'WW')) ) "Number of weeks" from dual

Number of weeks
---------------
              4

advertisements

 

SQL> /
Enter value for fromdt_dd_mon_yyyy: 01-Jan-2017
old   1: select  abs(to_number(to_char(to_date('&FromDT_dd_mon_yyyy','dd-mon-yyyy'), 'WW'))
new   1: select  abs(to_number(to_char(to_date('01-Jan-2017','dd-mon-yyyy'), 'WW'))
Enter value for todt_dd_mon_yyyy: 31-Dec-2017
old   2: - to_number(to_char(to_date('&ToDT_dd_mon_yyyy','dd-mon-yyyy'),'WW')) ) "Number of weeks" from dual
new   2: - to_number(to_char(to_date('31-Dec-2017','dd-mon-yyyy'),'WW')) ) "Number of weeks" from dual

Number of weeks
---------------

             52