Date counting

annamaria · #1 (**permalink**) 02-26-08, 07:24

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)

guyprzytula · #2 (**permalink**) 02-26-08, 07:49

you could use the dayofweek(x) function
date + 1 days can be used
see sql reference how to calculate with dates

stolze · #3 (**permalink**) 02-26-08, 10:29

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.

Peter.Vanroose · #4 (**permalink**) 02-26-08, 11:24

Quote:

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... ;-)

annamaria · #5 (**permalink**) 02-29-08, 04:33

Quote:

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)

Peter.Vanroose · #6 (**permalink**) 02-29-08, 05:14

Quote:

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.

tonkuma · #7 (**permalink**) 03-01-08, 14:49

Quote:

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.

annamaria · #8 (**permalink**) 12-26-08, 10:33

made in error

Internet Services

Web Development

Digital Marketing

Consumer Tech