1. Registered User
Join Date
Apr 2007
Posts
51

Is there a way in SQL for IBM Ozs to count days between two dates according to a specific calendar (for example, Italian banking holidays calendar)?
If there isn't, how could I count days between two dates without counting Saturdays and Sundays? In Excel there's the command DAY.WEEK with which week days are numbered from 1 (Monday) to 7 (Sunday) and I can instruct: "if weekday is <= 4 then weekday + 1 else weekday + 3. Of course Excel has a calendar beneath and knows that, say, 20-02-2008 is Wednesday (day 3 of the week).

Thank you

Anna - Verona (Italy)

2. Registered User
Join Date
Jun 2006
Posts
471
you could use the dayofweek(x) function
date + 1 days can be used
see sql reference how to calculate with dates

3. Registered User
Join Date
Jan 2007
Location
Jena, Germany
Posts
2,721
You may want to have a look at the TIMESTAMPDIFF function: http://publib.boulder.ibm.com/infoce...estampdiff.htm

It can be used to calculate the amount of days between two timestamps. Note that it assumes that each month has 30 days, which is not really very exact - but that's in the nature of this function's input.

4. Registered User
Join Date
Sep 2004
Location
Belgium
Posts
1,126

## Date counting in DB2 for z/OS

Originally Posted by annamaria
Is there a way in SQL for IBM z/OS to count days between two dates according to a specific calendar (for example, Italian banking holidays calendar)?
You'll indeed have to implement that function (UDF) yourself: there's no standard "NoOfWeekDays" function or so. Let alone one that knows about Italian banking holidays.

Ingredients which will have to be used are:

- the DAYS function. argument: an expression of datatype DATE; returns: an integer
The difference between two DAYS return values is the nr. of days "between" the two, i.e., it will be 0 for equal dates, 1 or -1 for two consecutive days, etc.

- the DAYOFWEEK function: argument: a DATE; returns: 1 for Sunday, 2 for Monday, ... 7 for Saturday.

- a (small) table summing up the Italian banking holidays. Single column, datatype DATE.

Around this, you may write a relatively simple UDF which returns the number of working days between two given dates.
Alternatively, also a single SELECT statement could do this; in that case it will need to be a recursive one, still using the three mentioned ingredients.
(Its implementation is left as an exercise... ;-)

5. Registered User
Join Date
Apr 2007
Posts
51
Originally Posted by Peter.Vanroose
You'll indeed have to implement that function (UDF) yourself: there's no standard "NoOfWeekDays" function or so. Let alone one that knows about Italian banking holidays.

Ingredients which will have to be used are:

- the DAYS function. argument: an expression of datatype DATE; returns: an integer
The difference between two DAYS return values is the nr. of days "between" the two, i.e., it will be 0 for equal dates, 1 or -1 for two consecutive days, etc.

- the DAYOFWEEK function: argument: a DATE; returns: 1 for Sunday, 2 for Monday, ... 7 for Saturday.

- a (small) table summing up the Italian banking holidays. Single column, datatype DATE.

Around this, you may write a relatively simple UDF which returns the number of working days between two given dates.
Alternatively, also a single SELECT statement could do this; in that case it will need to be a recursive one, still using the three mentioned ingredients.
(Its implementation is left as an exercise... ;-)

How do I create a table?
I mean I can set a list of the Italian banking holidays but how can I make these dates become records of a SQL table?
Select '2008-12-25','2008-12-26' as COLUMN 1? I don't think this is exact.
Thank you
Anna - Verona (Italy)

6. Registered User
Join Date
Sep 2004
Location
Belgium
Posts
1,126
Originally Posted by annamaria
How do I create a table?
Code:
```CREATE TABLE banking_holidays (d DATE NOT NULL) ;
INSERT INTO banking_holidays (d) VALUES ('2008-12-25') ;
INSERT INTO banking_holidays (d) VALUES ('2008-12-26') ;
...```
The table name and the column name (d) may be chosen, of course.
With this syntax, the table will be created in the default database; it is possible to an an "IN ..." at the end. See the SQL Reference Guide of your version of DB2 for the details.

7. Registered User
Join Date
Feb 2008
Location
Japan
Posts
3,483
CREATE TABLE banking_holidays (d DATE NOT NULL) ;
INSERT INTO banking_holidays (d) VALUES ('2008-12-25') ;
INSERT INTO banking_holidays (d) VALUES ('2008-12-26') ;
...
You can construct multiple rows by VALUES syntax on DB2 for LUW(at lest V5.2 or higher).
Code:
```INSERT INTO banking_holidays (d) VALUES
('2008-12-25')
,('2008-12-26')
,('2008-12-31')
...;```
Unfortunately, the syntax is not supported on DB2 for z/OS.
Last edited by tonkuma; 03-01-08 at 16:04.

8. Registered User
Join Date
Apr 2007
Posts
51