How do I do the smartest way to calculate the number of days between two dates and exclude weekends and holidays. With datepart I can exclude weekends, but holidays are stored in a table. How do I best exclude these dates? Does it work with dateddiff?

Example:

CREATE TABLE tblExcludeDates ( 
   ExceptionDate DATE, 
   Type  INTEGER );

INSERT tblExcludeDates VALUES ('2019-04-24', 1);
INSERT tblExcludeDates VALUES ('2019-04-25', 1);

Between the dates 2019-04-01 and 2019-04-30 the result should be 20 days if it excludes weekends and holidays from above table.

asked 13 Nov, 12:36

Rolle's gravatar image

Rolle
517364353
accept rate: 0%


I created an example that can be run in I-Sql. Maybe you need to create a stored procedure/function based on the example where the inDateFrom and inDateUntil will be the parameters.

Remark:
rownum is a column generated by the rowgenerator and should be with a underscore between row and num. You need to change that to make it work.

CREATE TABLE tblExcludeDates (  
  ExceptionDate DATE,   
  Type  INTEGER );

INSERT tblExcludeDates VALUES ('2019-04-24', 1);  
INSERT tblExcludeDates VALUES ('2019-04-25', 1);  

create variable inDateFrom date;  
create variable inDateUntil date;   

set inDateFrom  = '2019-04-01';  
set inDateUntil = '2019-04-30';

select count()  
from rowgenerator  
where  
       // dates in the given period  
       (inDateFrom + row_num - 1) between inDateFrom and inDateUntil  
       // only weekdays  
   and DatePart(Caldayofweek,inDateFrom + row_num - 1) <= 5  
       // skip holidays  
   and not exists(select 1 from tblExcludeDates where ExceptionDate = (inDateFrom + row_num - 1))  

    count() 
----------- 
         20 
permanent link

answered 14 Nov, 03:26

Frank's gravatar image

Frank
314111323
accept rate: 33%

edited 25 Nov, 15:36

Breck%20Carter's gravatar image

Breck Carter
29.4k487653960

Thanks for your reply. I don't get it to work with your example. Don't quite understand how you mean with rownum and why it should be used?

(25 Nov, 15:27) Rolle
Replies hidden
Comment Text Removed

What version of SQL Anywhere are you using? SQL Anywhere has a table called rowgenerator that contains a column named row_num.

You should replace rownum with an underscore between row and num. HTML removes the 8underscore in my example. With every second underscore used the text is made cursive between the two underscores

(25 Nov, 15:34) Frank
1

I added a pre tag and changed rownum to row_num.

(25 Nov, 15:38) Breck Carter
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×3

question asked: 13 Nov, 12:36

question was seen: 95 times

last updated: 25 Nov, 15:38