Wednesday, September 6, 2017

Query To Get The Working Dates and Number Working Days between Two Dates

SELECT
  start_date,
  end_date,
   GREATEST (NEXT_DAY (start_date, 'MON') - start_date - 3, 0) + ( (NEXT_DAY (end_date, 'MON') - NEXT_DAY (start_date, 'MON')) / 7) * 5 - GREATEST (NEXT_DAY (end_date, 'MON') - end_date - 3, 0) working_days
FROM (SELECT TO_DATE (:SDATE) start_date, TO_DATE (:EDATE) end_date FROM DUAL)

Input - SDATE : 01-SEP-2017
Input - EDATE : 05-SEP-2017

OUTPUT : 5

SELECT date1,TO_CHAR(date1,'Day') DAY FROM
(SELECT TO_DATE(:start_date,'DD-MON-YYYY') + level - 1 date1 FROM dual CONNECT BY level <= (TO_DATE(:end_date,'DD-MON-YYYY') - TO_DATE(:start_date,'DD-MON-YYYY'))+1)
WHERE 1=1
AND TO_CHAR(date1, 'DY') NOT IN ('SAT', 'SUN');


Input - SDATE : 01-SEP-2017
Input - EDATE : 05-SEP-2017

OUTPUT :
01-SEP-2017
02-SEP-2017
03-SEP-2017
04-SEP-2017
05-SEP-2017

No comments:

Post a Comment

Best Blogger TipsGet Flower Effect