Oracle Sql Query to Find Out 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

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

_____________________________________________________________________________________________________________________

0 comments:

Post a Comment

 

acehints.com Copyright 2011-17 All Rights Reserved | Site Map | Contact | Disclaimer | Google