{"id":298,"date":"2010-04-30T09:37:34","date_gmt":"2010-04-30T09:37:34","guid":{"rendered":"http:\/\/www.thestupidbox.com\/blog\/?p=298"},"modified":"2021-06-25T04:16:41","modified_gmt":"2021-06-25T04:16:41","slug":"number-of-working-days-oracle","status":"publish","type":"post","link":"http:\/\/thestupidbox.com\/blog\/number-of-working-days-oracle\/","title":{"rendered":"Number of working days – Oracle"},"content":{"rendered":"

Most of us would have come across this task. ie., to find the number of working days between two given dates.<\/p>\n

Method 1<\/strong>
\nI got a function to calculate the same from my colleague.<\/p>\n

function getDays(d1 IN DATE, d2 IN DATE) RETURN INTEGER\r\nIS\r\n   l_result INTEGER := 0;\r\nBEGIN\r\n   FOR r IN (1 .. trunc(d2)-trunc(d1)) LOOP\r\n       IF (to_char(trunc(d1)+r),'DY') NOT IN ('SAT','SUN')) THEN\r\n              l_result := l_result + 1;\r\n       END IF\r\n   END LOOP;\r\n   RETURN l_result;\r\nEND;\r\n<\/pre>\n
The algorithm involved is to iterate through the number of days between the two dates and check each day if it is a working day or not. It works neatly but performance is very poor. <\/p>\n
Method 2<\/strong>
\nWhen I Google it, got this<\/a><\/p>\n
I like the logic and the performance is improved but not up-to expectation. In my case <\/p>\n
all_objects<\/pre>\n
 returns a huge list. Also there is a restriction to the maximum output. ie., the all_objects count itself.<\/p>\n
Method 3<\/strong>
\nmodified the same query little bit like this will not have any restriction also the performance will be better than previous.<\/p>\n
select count(*) from\r\n                    (select level rnum from dual connect by level <= to_date('&1') - to_date('&2')+1 ) \r\n        where to_char( to_date('&2')+rnum-1, 'DY' ) not in ( 'SAT', 'SUN' )<\/pre>\n
Another advantage in using this query technique (over iteration\/loop) is, we can also find the number of working days excluding the public holidays\/leaves (provided in a table from the system)<\/p>\n
Method 4<\/strong>
\nThere is a catch in the above technique, even though it is faster comparatively, not highly recommended to use inside another select query. This one is not a function, instead its a formula :P as mentioned in this link<\/a>. Therefore can be easily used in the any select query.<\/p>\n
select \r\n(trunc(end_dt - start_dt) -\r\n (\r\n  (case\r\n   WHEN (8-to_number(to_char(start_dt,'D') )) > trunc(end_dt - start_dt)+1\r\n   THEN 0\r\n   ELSE\r\n    trunc( (trunc(end_dt - start_dt) - (8-to_number(to_char(start_dt,'D') ))) \/ 7 ) + 1\r\n   END) +\r\n  (case\r\n   WHEN mod(8-to_char(start_dt,'D'),7) > trunc(end_dt - start_dt)-1\r\n   THEN 0\r\n   ELSE\r\n    trunc( (trunc(end_dt-start_dt) - (mod(8-to_char(start_dt,'D'),7)+1)) \/ 7 ) + 1\r\n   END)\r\n )\r\n) workingdays\r\nfrom table<\/pre>\n
This method gives good performance and the only limitation is excluding the working holidays.<\/p>\n
Conclusion<\/strong>:
\nMethod 3 and Method 4 can be used depends on the situation.<\/p>\n
Happy Quering!!<\/p>\n","protected":false},"excerpt":{"rendered":"
Most of us would have come across this task. ie., to find the number of working days between two given dates. Method 1 I got a function to calculate the same from my colleague. function getDays(d1 IN DATE, d2 IN… <\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[58],"tags":[72,70,71,73],"class_list":["post-298","post","type-post","status-publish","format-standard","hentry","category-technical","tag-exclude-holidays","tag-number-of-working-days","tag-oracle-working-days-exclude-holidays","tag-working-days-between-days"],"_links":{"self":[{"href":"http:\/\/thestupidbox.com\/blog\/wp-json\/wp\/v2\/posts\/298","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/thestupidbox.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/thestupidbox.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/thestupidbox.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/thestupidbox.com\/blog\/wp-json\/wp\/v2\/comments?post=298"}],"version-history":[{"count":14,"href":"http:\/\/thestupidbox.com\/blog\/wp-json\/wp\/v2\/posts\/298\/revisions"}],"predecessor-version":[{"id":312,"href":"http:\/\/thestupidbox.com\/blog\/wp-json\/wp\/v2\/posts\/298\/revisions\/312"}],"wp:attachment":[{"href":"http:\/\/thestupidbox.com\/blog\/wp-json\/wp\/v2\/media?parent=298"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/thestupidbox.com\/blog\/wp-json\/wp\/v2\/categories?post=298"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/thestupidbox.com\/blog\/wp-json\/wp\/v2\/tags?post=298"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}