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

_____________________________________________________________________________________________________________________

The Eucharistic Miracles of the World
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

_____________________________________________________________________________________________________________________

Website Stats

0 comments:

Post a Comment

Labels

Oracle (578) General (60) Unix (47) Script (41) Blog (23) OCI (3) SQL* Loader (3) Datapump (2) Software (1)
DBA Tips
 

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