DB2
z/OS DB2 LUW
z/OS
CICS VTAM
|
DB2 Datumsberechnung
|
Home |
Kleines Beispiel zum
Errechnen
des ersten und des
letzetn Tages eines Monats !
Erster Tag
eines Monats
Select DATE(Current date - DAY(Current date) DAY + 1 Day)
from
sysibm.sysdummy1 ;
oder
Select date('01'
Concat '.'
Concat char(Substr(DIGITS(Month(Current Date)), 9, 2), 2)
concat '.'
concat char(Substr(DIGITS(year(Current Date)), 7, 4), 4)
)
from sysibm.sysdummy1
Letzter Tag
eines Monats
Select DATE((Current date +1 MONTH -DAY(Current date) DAY)) from
sysibm.sysdummy1 ;
Letzter Tag des vorherigen
Monats
Select DATE(Current date - DAY(Current date) days ) from
sysibm.sysdummy1 ;
Letzter Arbeitstag im
Monat (Montag bis
Freitag)
Select case when dayofweek_Iso(Letzter_eines_Monats) > 5
Then -- Samstag, Sonntag
Letzter_eines_Monats - (dayofweek_Iso(Letzter_eines_Monats) -
5) days
Else
Letzter_eines_Monats
End
from (Values DATE((Current Date +1 MONTH - DAY(Current Date) DAY))
,
Date('31.07.2011')
) Tag (Letzter_eines_Monats)
;
oder so
With Datum_Tab (Letzter_eines_Monats) AS
(Select Date('31.01.' concat Char(Year(Current
Date))
)
From sysibm.sysdummy1
Union All
Select DATE(((Letzter_eines_Monats + 1 Month)
+ 1 MONTH -
DAY(Letzter_eines_Monats + 1 Month) DAY))
From Datum_Tab
Where Year(Letzter_eines_Monats) =
year(Current Date)
)
Select Letzter_eines_Monats
, Dayname(Letzter_eines_Monats) dayname
, case when
dayofweek_Iso(Letzter_eines_Monats) > 5 Then -- Samstag,
Sonntag
Letzter_eines_Monats - (dayofweek_Iso(Letzter_eines_Monats) - 5)
days
Else
Letzter_eines_Monats
End AS letzterArbeitstag
from datum_Tab
Where Year(Letzter_eines_Monats) = year(Current Date)
;
Letzte n Tage anzeigen (im Beispiel 5
Tage)
with
TAGE (DATUM) AS
(SELECT current date - 5 Days
from sysibm.sysdummy1
union all
select Datum + 1 Day
From TAGE
where datum < Current
date
)
Select datum, dayname(datum)
from Tage
;
z/OS
WITH
TAGE (DATUM, LETZTER ) AS
(
SELECT DATE('01.01.' concat
char(year(Current date)))
,
DATE('31.01.' concat char(year(Current date)))
FROM SYSIBM.SYSDUMMY1
UNION ALL
SELECT DATE(DATUM + 1 MONTH)
, DATE(DATUM + 2 MONTH - DAY(DATUM) DAY)
FROM TAGE
WHERE YEAR(DATUM) =
YEAR(CURRENT DATE)
)
SELECT DATUM, LETZTER, LETZTER_WOCHENTAG
, Dayname(LETZTER_WOCHENTAG) As Weekday
from (
SELECT DATUM, LETZTER
, CASE WHEN DAYOFWEEK_ISO(LETZTER) > 5 THEN
LETZTER - (DAYOFWEEK_ISO(LETZTER) - 5) DAYS
ELSE
LETZTER
END AS LETZTER_WOCHENTAG
FROM TAGE
) T1
WHERE YEAR(DATUM) = YEAR(CURRENT DATE)
;
Oder !
Select Last_Working_Day
, Case when dayofweek(Last_Working_Day)
= 1 Then
'Sonntag'
when
dayofweek(Last_Working_Day) = 2 Then 'Montag'
when
dayofweek(Last_Working_Day) = 3 Then 'Dienstag'
when
dayofweek(Last_Working_Day) = 4 Then 'Mittwoch'
when
dayofweek(Last_Working_Day) = 5 Then 'Donnerstg'
when
dayofweek(Last_Working_Day) = 6 Then 'Freitag'
Else
'SAMSTAG'
End as
Wochentag
, Dayname(Last_Working_Day) As Weekday
From (
select case when dayofweek(DATE((Current date +1 MONTH -DAY(Current
date) DAY))) = 1 Then -- Sonntag
date(DATE((Current date +1 MONTH -DAY(Current date) DAY)) -2 days)
when
dayofweek(DATE((Current date +1 MONTH -DAY(Current date) DAY))) = 7
Then -- Samstag
date(DATE((Current date +1 MONTH -DAY(Current date) DAY)) -1 days)
else DATE((Current date +1 MONTH -DAY(Current date) DAY))
End As Last_working_day
from sysibm.sysdummy1
) T1
;
Anzahl Tage im Jahr :
--(31.12.2011 - 01.01.2011 + 1)
select Char(Jahr, Eur) AS JAHR,
Days(Date('31.12.' concat
year(Jahr)))
- Days(Date('01.01.' concat year(Jahr)))
+ 1 AS ANZTAGE
from (Values DATE('31.07.2010')
, Date('31.07.2011')
,
Date('31.07.2012')
,
Date('31.07.2013')
) Tag (Jahr)
;
oder so
--(01.01.1900 -01.01.2200)
WITH
Jahre (JAHR) AS (
SELECT DATE('01.01.1900')
FROM SYSIBM.SYSDUMMY1
UNION ALL
SELECT DATE(Jahr + 1 YEAR)
FROM Jahre
WHERE YEAR(Jahr) < 2200
)
select Char(Jahr, Eur) AS JAHR,
Days(Date('31.12.' concat
year(Jahr)))
- Days(Date('01.01.' concat year(Jahr)))
+ 1 AS ANZTAGE
from Jahre
-- where year(jahr) = year(Current date)
;
Function um Ostersonntag
zu berechnen
!
Ostern mit DB2 SQL berechnen !
Die Functions laufen auch mit Timestamp einfach als zweite Function
definieren
1.) (CHECKDATE TIMESTAMP )
2.
SPECIFIC DB2ADMIN.OSTERN_TIMESTAMP
Aufruf der function
select db2admin.ostern(Current date) from sysibm.sysdummy1 ;
select db2admin.ostern(Current date + 2 year) from sysibm.sysdummy1 ;
select db2admin.ostern('01.01.2010') from sysibm.sysdummy1 ;
CREATE FUNCTION DB2ADMIN.OSTERN -- (DB2 LUW)
("CHECKDATE" DATE )
RETURNS DATE
SPECIFIC DB2ADMIN.OSTERN_DATE
LANGUAGE SQL
DETERMINISTIC
READS SQL DATA
STATIC DISPATCH
CALLED ON NULL INPUT
NO EXTERNAL ACTION
INHERIT SPECIAL REGISTERS
RETURN
Select Date('21.03.' concat char(Substr(DIGITS(year(CHECKDATE)), 7, 4),
4)) + (D + e + 1) Days
from (
select a, b, c, m, s, n, d, MOD((2 * b +
4 * c + 6 *
D + N) , 7) AS e
from (
Select a, b, c, n, m, s ,
case
when MOD((M + 19 * A) , 30) = 29 Then 28
when MOD((M + 19 * A) , 30) = 28 And A >= 11
Then 27
Else MOD((M + 19 * A) , 30)
END
AS D
from(
Select A, B, C, S, M1 , MOD((15 + s - m1) , 30 ) as M , MOD(( 6
+
s) , 7) AS N
From(
select MOD(Year(CHECKDATE) , 19) as A , MOD(Year(CHECKDATE) ,
4) as B
, MOD(Year(CHECKDATE) , 7) as C
, INT(( 8 * ( Year(CHECKDATE) / 100) + 13) / 25 -
2) as M1
, INT ((Year(CHECKDATE) / 100) - (Year(CHECKDATE)
/ 400) -
2) AS S
from sysibm.sysdummy1 ) T4 ) T3 ) T2 ) T1 ;
oder ohne sysdummy !
CREATE FUNCTION "DB2ADMIN"."OSTERN"
("CHECKDATE" DATE )
RETURNS DATE
SPECIFIC "DB2ADMIN"."OSTERN"
LANGUAGE SQL
DETERMINISTIC
CONTAINS SQL
STATIC DISPATCH
CALLED ON NULL INPUT
NO EXTERNAL ACTION
INHERIT SPECIAL REGISTERS
BEGIN ATOMIC
DECLARE OSTERN DATE ;
DECLARE A INT ;
DECLARE B INT ;
DECLARE C INT ;
DECLARE D INT ;
DECLARE E INT ;
DECLARE M INT ;
DECLARE N INT ;
DECLARE S INT ;
DECLARE JAHR CHAR(4);
SET A = MOD(Year(CHECKDATE) , 19) ;
SET B = MOD(Year(CHECKDATE) , 4) ;
SET C = MOD(Year(CHECKDATE) , 7) ;
SET M = INT(( 8 * ( Year(CHECKDATE) / 100) + 13) /
25 - 2) ;
SET S = INT ((Year(CHECKDATE) / 100) -
(Year(CHECKDATE) / 400) - 2) ;
SET M = MOD((15 + s - m) , 30 ) ;
SET N = MOD(( 6 + s) , 7) ;
SET D = case when MOD((M + 19 * A)
, 30)
= 29
Then 28
when
MOD((M + 19 * A) , 30)
= 28 And A >= 11 Then 27
Else MOD((M + 19 * A) , 30)
END ;
SET E = MOD((2 * b + 4 * c + 6 * D + N) , 7) ;
SET JAHR = char(Substr(DIGITS(year(CHECKDATE)), 7,
4), 4) ;
SET OSTERN = Date('21.03.' concat JAHR) + (D + e +
1) Days ;
RETURN OSTERN ;
END;
Oder so hab ich die Function unter z/OS
laufen !
CREATE FUNCTION ADMINUSER.OSTERN
("CHECKDATE" DATE)
RETURNS DATE
SPECIFIC ADMINUSER.OSTERN
LANGUAGE SQL
NOT DETERMINISTIC
READS SQL DATA
STATIC DISPATCH
CALLED ON NULL INPUT
EXTERNAL ACTION
RETURN
Date('21.03.' concat char(Substr(DIGITS(year(CHECKDATE)), 7, 4),
4))
+ ((
case
when MOD(((MOD((15 + (INT
((Year(CHECKDATE) / 100) -
( Year(CHECKDATE ) / 400) -
2)) -
(INT(( 8 *
( Year( CHECKDATE) /
100) + 13) / 25
- 2 ))) , 30 )) + 19 *
(
MOD(Year(CHECKDATE) , 19)))
, 30) = 29
Then 28
when MOD(((MOD((15 + (INT
((Year(CHECKDATE) / 100) -
( Year(CHECKDATE ) / 400) -
2)) -
(INT(( 8 *
( Year( CHECKDATE) / 100)
+ 13) / 25
- 2 ))) , 30 )) + 19 *
(
MOD(Year(CHECKDATE) , 19)))
, 30) = 28 And
(MOD(Year(CHECKDATE) , 19)) >= 11
Then 27
Else MOD(((MOD((15 + (INT
((Year(CHECKDATE) / 100) -
( Year(CHECKDATE ) / 400)
- 2)) -
(INT(( 8 * (
Year( CHECKDATE) / 100) + 13) / 25 -
2)) ) , 30 )) + 19 *
( MOD(Year( CHECKDATE) , 19)))
,
30)
END)
+ (MOD((2 * (MOD(Year(CHECKDATE) , 4)) + (4 * (MOD( Year
(CHECKDATE ) , 7))) + (6 * (
case
when MOD(((MOD((15 + (INT
((Year(CHECKDATE) / 100) -
( Year
(CHECKDATE ) / 400) - 2)) -
(INT(( 8 * (
Year( CHECKDATE) / 100) + 13) / 25
- 2 ))) , 30 )) + 19 *
(MOD(Year(CHECKDATE) , 19)))
, 30) = 29
Then 28
when MOD(((MOD((15 + (INT
((Year(CHECKDATE) / 100) -
( Year
(CHECKDATE ) / 400) - 2)) -
(INT(( 8 *
( Year( CHECKDATE) /
100) + 13) / 25
- 2 ))) , 30 )) + 19 *
(MOD(Year(CHECKDATE) , 19)))
, 30) = 28 And
(MOD(Year(CHECKDATE) , 19)) >= 11
Then 27
Else MOD(((MOD((15 + (INT
((Year(CHECKDATE) / 100) -
(
Year(CHECKDATE ) / 400)
- 2)) -
(INT(( 8
* (
Year( CHECKDATE) / 100) + 13) / 25 -
2)) ) , 30 )) + 19 *
(
MOD(Year( CHECKDATE) , 19))) ,
30)
END))
+ (MOD(( 6 + (INT ((Year(CHECKDATE) / 100) - (Year(
CHECKDATE) /
400) - 2))) , 7))) , 7))
+ 1) Days;
COMMIT ;
GRANT EXECUTE ON SPECIFIC FUNCTION ADMINUSER.OSTERN TO PUBLIC;
COMMIT;
Z/OS etwas übersichtlicher
WITH
TAB1(A, B, C, M1, S) AS
(
select MOD(Year(CURRENT DATE) ,
19) as A
, MOD(Year(CURRENT DATE) , 4)
as B
, MOD(Year(CURRENT DATE) , 7)
as C
, INT(( 8 * ( Year(CURRENT DATE) /
100) + 13) / 25 - 2) as M1
, INT ((Year(CURRENT DATE) / 100)
-
(Year(CURRENT DATE) / 400) - 2) as S
from sysibm.sysdummy1
)
, TAB2(a, b, c, s, m, n) AS
(Select A, B, C, S
,
MOD((15 + s - m1) , 30 ) as M
,
MOD(( 6 + s) , 7) AS N
FROM TAB1
)
, TAB3(a, b, c, s, m, n, d ) AS
(Select a, b, c, s, m, n,
case
when
MOD((M + 19 * A) , 30) = 29 Then 28
when
MOD((M + 19 * A) , 30) = 28 And A >= 11 Then 27
Else
MOD((M + 19 * A) , 30)
END AS d
FROM TAB2
)
, TAB4(a, b, c, s, m, n, d, e) AS
(
select a, b, c, s, m, n, d
,
MOD((2 * b + 4 * c + 6 * D + N) , 7) AS e
from TAB3
)
Select Date('21.03.' concat
char(Substr(DIGITS(year(CURRENT DATE)),
7, 4), 4)) +
(D + e +
1) Days
from TAB4
;
Als View geht das natürlich auch (LUW und z/OS) Ostern von 1900 -
2200
Create View DB2ADMIN.VW_OSTERN (JAHR, OSTERN) AS
WITH
TAGE (JAHR, CHECKDATE ) AS (
SELECT 1900, DATE('01.01.1900')
FROM SYSIBM.SYSDUMMY1
UNION ALL
SELECT JAHR + 1, DATE(CHECKDATE +
1 YEAR)
FROM TAGE
WHERE YEAR(CHECKDATE) <
2201
)
Select JAHR
, Date('21.03.' concat
char(Substr(DIGITS(year(CHECKDATE)),
7, 4), 4))
+ ((
case
when MOD(((MOD((15 + (INT ((Year(CHECKDATE) / 100) -
(
Year(CHECKDATE ) / 400) - 2)) - (INT(( 8 *
(
Year( CHECKDATE) / 100) + 13) / 25-2 ))) , 30)) + 19 *
(
MOD(Year(CHECKDATE) , 19))) , 30) = 29
Then 28
when MOD(((MOD((15 + (INT ((Year(CHECKDATE) / 100) -
(
Year(CHECKDATE ) / 400) - 2)) - (INT(( 8 *
( Year(
CHECKDATE) / 100) + 13) / 25 - 2 ))) , 30)) + 19 *
(
MOD(Year(CHECKDATE) , 19))) , 30) = 28
And (MOD(Year(CHECKDATE) , 19)) >= 11
Then 27
Else MOD(((MOD((15 + (INT ((Year(CHECKDATE) / 100) -
( Year(CHECKDATE
) / 400) - 2)) -
(INT(( 8 * (
Year( CHECKDATE) / 100) + 13) / 25 - 2)) )
, 30 ))
+ 19 * ( MOD(Year( CHECKDATE) , 19))) , 30)
END)
+ (MOD((2 * (MOD(Year(CHECKDATE) , 4)) + (4 *
(MOD( Year
(CHECKDATE ) , 7)))
+ (6 * (
case
when MOD(((MOD((15 + (INT
((Year(CHECKDATE) / 100) -
( Year (CHECKDATE ) / 400) - 2)) - (INT(( 8 *
(Year( CHECKDATE) / 100) + 13) / 25 - 2 )))
, 30 ))
+ 19 * (MOD(Year(CHECKDATE) , 19))) , 30) = 29
Then 28
when MOD(((MOD((15 + (INT
((Year(CHECKDATE) / 100) - ( Year
(CHECKDATE
) / 400) - 2)) - (INT(( 8 *
(
Year( CHECKDATE) / 100) + 13) / 25 - 2 ))) , 30)) + 19 *
(MOD(Year(CHECKDATE) , 19))) , 30) = 28
And (MOD(Year(CHECKDATE) ,
19)) >= 11
Then 27
Else MOD(((MOD((15 + (INT ((Year(CHECKDATE) / 100) -
( Year(CHECKDATE ) / 400) - 2)) -
(INT(( 8 * (Year( CHECKDATE) / 100) + 13) / 25-2))
) , 30 )) + 19 *
( MOD(Year( CHECKDATE) , 19))) , 30)
END))
+ (MOD(( 6 + (INT ((Year(CHECKDATE) / 100) -
(Year(
CHECKDATE) / 400) - 2))) , 7))) , 7))
+ 1) Days
From TAGE
;
Feste- und variable- Feiertage mit der
Function
Ostern berechnen !
Current date = '29.10.2010'
select ostern(Current date) - 52 Days as Weiberfastnacht
, ostern(Current
date) - 48 Days as Rosenmontag
, ostern(Current
date) - 2 Days as Karfreitag
, ostern(Current
date)
as Ostersonnatg
, ostern(Current
date) + 1 Days as Ostermontag
, ostern(Current
date) + 39 Days as Himmelfahrt
, ostern(Current
date) + 50 Days as Pfingstmontag
, ostern(Current
date) + 60 Days as Fronleichnam
, DATE('01.01.' concat char(year(current
date))) AS Neujahr
, DATE('03.10.' concat
char(year(current date))) AS TagDerDeutschenEinheit
, DATE('01.11.' concat char(year(current
date))) AS Allerheiligen
, DATE('24.12.' concat
char(year(current date))) AS Weihnachten
, DATE('25.12.' concat char(year(current
date))) AS Weihnachtsfeiertag#1
, DATE('26.12.' concat
char(year(current date))) AS Weihnachtsfeiertag#2
, DATE('31.12.' concat
char(year(current date))) AS Silvester
from sysibm.sysdummy1
;
ODER SO
Select Char(Datum, Eur) as Datum, Watt, Wochentag
from (
Values (OSTERN(Current date) - 52 Days ,
'Weiberfastnacht')
, (OSTERN(Current date) - 48
Days , 'Rosenmontag')
, (OSTERN(Current
date) - 2 Days , 'Karfreitag')
, (OSTERN(Current
date)
, 'Ostersonnatg')
, (OSTERN(Current
date) + 1 Days , 'Ostermontag')
, (OSTERN(Current
date) + 39 Days , 'Himmelfahrt')
, (OSTERN(Current
date) + 50 Days , 'Pfingstmontag')
, (OSTERN(Current
date) + 60 Days , 'Fronleichnam')
, (DATE('01.01.' concat
char(year(current date))) , 'Neujahr' )
, (DATE('31.12.' concat
char(year(current date))) , 'Silvester' )
, (DATE('24.12.' concat
char(year(current date))) , 'Weihnachten' )
, (DATE('25.12.' concat
char(year(current date))) , '1. Weihnachtsfeiertag')
, (DATE('26.12.' concat
char(year(current date))) , '2. Weihnachtsfeiertag')
, (DATE('03.10.' concat
char(year(current date))) , 'Tag der deutschen einheit')
, (DATE('01.11.' concat
char(year(current date))) , 'Allerheiligen')
) Feiertage (Datum, Watt)
Inner Join(
Values (1, 'Sonntag')
, (2, 'Montag')
, (3, 'Dienstag')
, (4, 'Mittwoch')
, (5, 'Donnerstag')
, (6, 'Freitag')
, (7, 'Samstag')
) Tage ( DayInd, Wochentag)
On DAYOFWEEK(Datum) = DayInd
order by Date(Datum)
;
Kleine Function um Arbeitstage eines
Monats zu berechnen (MO-FR)
(Feiertage werden nicht berücksichtigt)
CREATE FUNCTION DB2ADMIN.ARBEITSTAGE
("CHECKDATE" DATE )
RETURNS INTEGER
SPECIFIC DB2ADMIN.ARBEITSTAGE
LANGUAGE SQL
DETERMINISTIC
CONTAINS SQL
STATIC DISPATCH
CALLED ON NULL INPUT
NO EXTERNAL ACTION
INHERIT SPECIAL REGISTERS
BEGIN ATOMIC
DECLARE WORKDAY INTEGER;
DECLARE FIRSTDATE DATE;
DECLARE LASTDATE DATE;
DECLARE EODATE INT;
SET EODATE = 0;
If (Month(CHECKDATE) = 01 and year(CHECKDATE) = 01 )
Then
SET FIRSTDATE =
DATE('01.01.0001') ;
Else
SET FIRSTDATE =
DATE(CHECKDATE - DAY(CHECKDATE) DAY + 1 Day) ;
End if ;
If (Month(CHECKDATE) = 12 and year(CHECKDATE) = 9999
) Then
SET LASTDATE = DATE('31.12.9999') ;
Else
SET LASTDATE = DATE((CHECKDATE + 1 MONTH
-DAY(CHECKDATE) DAY)) ;
End if ;
SET WORKDAY = 0 ;
WHILE EODATE = 0 DO
If DAYOFWEEK_ISO(FIRSTDATE) < 6
THEN
SET WORKDAY = WORKDAY + 1 ;
end if;
If FIRSTDATE >=
LASTDATE Then
SET EODATE = 1;
Else
SET FIRSTDATE = FIRSTDATE +
1 Day;
End if ;
END WHILE;
RETURN WORKDAY ;
END;
Kleine Function um Arbeitstage eines
Monats zu berechnen (MO-FR)
(Feiertage werden berücksichtigt basierend auf Ostern)
Im Leben habe ich eine Feiertagstabelle aber zum Testen habe ich jetzt
einfach mal die
Funktion Ostern benutzt.
CREATE FUNCTION DB2ADMIN.ARBEITSTAGEF
("CHECKDATE" DATE )
RETURNS INTEGER
SPECIFIC DB2ADMIN.ARBEITSTAGEF
LANGUAGE SQL
DETERMINISTIC
READS SQL DATA
STATIC DISPATCH
CALLED ON NULL INPUT
NO EXTERNAL ACTION
INHERIT SPECIAL REGISTERS
BEGIN ATOMIC
DECLARE WORKDAY INTEGER;
DECLARE FIRSTDATE DATE;
DECLARE LASTDATE DATE;
DECLARE OSTER_SONNTAG DATE ;
DECLARE EODATE INT;
SET EODATE = 0;
If (Month(CHECKDATE) = 1
and year(CHECKDATE) = 1 ) Then
SET FIRSTDATE = DATE('01.01.0001') ;
Else
SET FIRSTDATE = DATE(CHECKDATE -
DAY(CHECKDATE) DAY + 1 Day) ;
End if ;
If (Month(CHECKDATE) = 12
and year(CHECKDATE) = 9999 ) Then
SET LASTDATE = DATE('31.12.9999') ;
Else
SET LASTDATE = DATE((CHECKDATE + 1 MONTH
-DAY(CHECKDATE) DAY)) ;
End if ;
SET WORKDAY = 0 ;
SET OSTER_SONNTAG = OSTERN(CHECKDATE) ;
WHILE EODATE = 0 DO
If DAYOFWEEK_ISO(FIRSTDATE) > 5 THEN
SET WORKDAY = WORKDAY + 0 ;
ELSEIf FIRSTDATE IN (
Select Feiertag
from
(
Values (OSTER_SONNTAG - 2 Days) -- Karfreitag
,
(OSTER_SONNTAG)
-- Ostern
, (OSTER_SONNTAG + 1 Days) -- Ostermontag
, (OSTER_SONNTAG + 39 Days) -- Himmelfahrt
, (OSTER_SONNTAG + 50 Days) --Pfingstmontag
, (OSTER_SONNTAG + 60 Days) -- Fronleichnam
, (DATE('01.01.' concat char(Substr(DIGITS(year(CHECKDATE)), 7, 4), 4))
)
, (DATE('03.10.' concat char(Substr(DIGITS(year(CHECKDATE)), 7, 4), 4))
)
, (DATE('01.11.' concat char(Substr(DIGITS(year(CHECKDATE)), 7, 4), 4))
)
, (DATE('24.12.' concat char(Substr(DIGITS(year(CHECKDATE)), 7, 4), 4))
)
, (DATE('25.12.' concat char(Substr(DIGITS(year(CHECKDATE)), 7, 4), 4))
)
, (DATE('26.12.' concat char(Substr(DIGITS(year(CHECKDATE)), 7, 4), 4))
)
, (DATE('31.12.' concat char(Substr(DIGITS(year(CHECKDATE)), 7, 4), 4))
)
) Feiertage (Feiertag) ) THEN
SET WORKDAY = WORKDAY + 0 ;
ELSE
SET WORKDAY = WORKDAY + 1 ;
end if;
If FIRSTDATE >= LASTDATE Then
SET EODATE = 1;
Else
SET FIRSTDATE = FIRSTDATE +
1 Day;
End if ;
END WHILE;
RETURN WORKDAY ;
END;
Aufruf : select DB2ADMIN.ARBEITSTAGE(Date('31.12.2012'))
From
sysibm.sysdummy1 ;
Gibt 21 zurück !
Aufruf : select DB2ADMIN.ARBEITSTAGEF(Date('31.12.2012'))
From
sysibm.sysdummy1 ;
Gibt 17 zurück !
Kleine Function um den ersten
Arbeitstage eines
Monats zu berechnen (MO-FR)
(Feiertage werden berücksichtigt basierend auf Ostern)
CREATE FUNCTION "DB2ADMIN"."ERSTER_ARBEITSTAG"
("CHECKDATE" DATE )
RETURNS DATE
SPECIFIC "DB2ADMIN"."ERSTER_ARBEITSTAG"
LANGUAGE SQL
DETERMINISTIC
READS SQL DATA
STATIC DISPATCH
CALLED ON NULL INPUT
NO EXTERNAL ACTION
INHERIT SPECIAL REGISTERS
BEGIN ATOMIC
DECLARE WORKDAY DATE;
DECLARE FIRSTDATE DATE;
DECLARE LASTDATE DATE;
DECLARE OSTER_SONNTAG DATE ;
DECLARE EODATE INT;
SET EODATE = 0;
If (Month(CHECKDATE) = 1
and year(CHECKDATE) = 1 ) Then
SET FIRSTDATE = DATE('01.01.0001') ;
Else
SET FIRSTDATE = DATE(CHECKDATE -
DAY(CHECKDATE) DAY + 1 Day) ;
End if ;
If (Month(CHECKDATE) = 12
and year(CHECKDATE) = 9999 ) Then
SET LASTDATE = DATE('31.12.9999') ;
Else
SET LASTDATE = DATE((CHECKDATE + 1 MONTH
-DAY(CHECKDATE) DAY)) ;
End if ;
SET OSTER_SONNTAG = OSTERN(CHECKDATE) ;
WHILE EODATE = 0 DO
If DAYOFWEEK_ISO(FIRSTDATE) > 5 THEN
SET EODATE = 0;
ELSEIf FIRSTDATE IN (
Select Feiertag
from
(
Values (OSTER_SONNTAG - 2 Days) -- Karfreitag
,
(OSTER_SONNTAG)
-- Ostern
, (OSTER_SONNTAG + 1 Days) -- Ostermontag
, (OSTER_SONNTAG + 39 Days) -- Himmelfahrt
, (OSTER_SONNTAG + 50 Days) --Pfingstmontag
, (OSTER_SONNTAG + 60 Days) -- Fronleichnam
, (DATE('01.01.' concat char(Substr(DIGITS(year(CHECKDATE)), 7, 4), 4))
)
, (DATE('03.10.' concat char(Substr(DIGITS(year(CHECKDATE)), 7, 4), 4))
)
,
(DATE('01.11.' concat char(Substr(DIGITS(year(CHECKDATE)), 7, 4), 4)) )
, (DATE('24.12.' concat char(Substr(DIGITS(year(CHECKDATE)), 7, 4), 4))
)
, (DATE('25.12.' concat char(Substr(DIGITS(year(CHECKDATE)), 7, 4), 4))
)
, (DATE('26.12.' concat char(Substr(DIGITS(year(CHECKDATE)), 7, 4), 4))
)
, (DATE('31.12.' concat char(Substr(DIGITS(year(CHECKDATE)), 7, 4), 4))
)
) Feiertage (Feiertag) ) THEN
SET EODATE = 0;
ELSE
SET EODATE = 1;
SET WORKDAY = FIRSTDATE ;
end if;
SET FIRSTDATE = FIRSTDATE + 1 Day;
END WHILE;
RETURN WORKDAY ;
END;
Kleine Function um den
letzten Arbeitstage eines
Monats zu berechnen (MO-FR)
(Feiertage werden berücksichtigt basierend auf Ostern)
CREATE FUNCTION "DB2ADMIN"."LETZTER_ARBEITSTAG"
("CHECKDATE" DATE )
RETURNS DATE
SPECIFIC "DB2ADMIN"."LETZTER_ARBEITSTAG"
LANGUAGE SQL
DETERMINISTIC
READS SQL DATA
STATIC DISPATCH
CALLED ON NULL INPUT
NO EXTERNAL ACTION
INHERIT SPECIAL REGISTERS
BEGIN ATOMIC
DECLARE WORKDAY DATE;
DECLARE FIRSTDATE DATE;
DECLARE LASTDATE DATE;
DECLARE OSTER_SONNTAG DATE ;
DECLARE EODATE INT;
SET EODATE = 0;
If (Month(CHECKDATE) = 1
and year(CHECKDATE) = 1 ) Then
SET FIRSTDATE = DATE('01.01.0001') ;
Else
SET FIRSTDATE = DATE(CHECKDATE -
DAY(CHECKDATE) DAY + 1 Day) ;
End if ;
If (Month(CHECKDATE) = 12
and year(CHECKDATE) = 9999 ) Then
SET LASTDATE = DATE('31.12.9999') ;
Else
SET LASTDATE = DATE((CHECKDATE + 1 MONTH
-DAY(CHECKDATE) DAY)) ;
End if ;
SET OSTER_SONNTAG = OSTERN(CHECKDATE) ;
WHILE EODATE = 0 DO
If DAYOFWEEK_ISO(LASTDATE) > 5 THEN
SET EODATE = 0;
ELSEIf LASTDATE IN (
Select Feiertag
from
(
Values (OSTER_SONNTAG - 2 Days) -- Karfreitag
,
(OSTER_SONNTAG)
-- Ostern
, (OSTER_SONNTAG + 1 Days) -- Ostermontag
, (OSTER_SONNTAG + 39 Days) -- Himmelfahrt
, (OSTER_SONNTAG + 50 Days) --Pfingstmontag
, (OSTER_SONNTAG + 60 Days) -- Fronleichnam
, (DATE('01.01.' concat char(Substr(DIGITS(year(CHECKDATE)), 7, 4), 4))
)
, (DATE('03.10.' concat char(Substr(DIGITS(year(CHECKDATE)), 7, 4), 4))
)
, (DATE('01.11.' concat
char(Substr(DIGITS(year(CHECKDATE)), 7, 4), 4)) )
, (DATE('24.12.' concat char(Substr(DIGITS(year(CHECKDATE)), 7, 4), 4))
)
, (DATE('25.12.' concat char(Substr(DIGITS(year(CHECKDATE)), 7, 4), 4))
)
, (DATE('26.12.' concat char(Substr(DIGITS(year(CHECKDATE)), 7, 4), 4))
)
, (DATE('31.12.' concat char(Substr(DIGITS(year(CHECKDATE)), 7, 4), 4))
)
) Feiertage (Feiertag) ) THEN
SET EODATE = 0;
ELSE
SET EODATE = 1;
SET WORKDAY = LASTDATE ;
end if;
SET LASTDATE = LASTDATE - 1 Day;
END WHILE;
RETURN WORKDAY ;
END;
Wochentage eines Monats zaehlen :
Die Wochentage basieren auf "DAYOFWEEK_ISO", also MO=1, DI=2, ...
SO=7
CREATE FUNCTION DB2ADMIN.WOCHENTAGE
("CHECKDATE" DATE, WOCHENTAG INTEGER )
RETURNS INTEGER
SPECIFIC DB2ADMIN.WOCHENTAGE
LANGUAGE SQL
DETERMINISTIC
CONTAINS SQL
STATIC DISPATCH
CALLED ON NULL INPUT
NO EXTERNAL ACTION
INHERIT SPECIAL REGISTERS
BEGIN ATOMIC
DECLARE WORKDAY INTEGER;
DECLARE FIRSTDATE DATE;
DECLARE LASTDATE DATE;
DECLARE EODATE INT;
SET EODATE = 0;
If (Month(CHECKDATE) = 01
and year(CHECKDATE) = 01 ) Then
SET FIRSTDATE = DATE('01.01.0001') ;
Else
SET FIRSTDATE = DATE(CHECKDATE -
DAY(CHECKDATE) DAY + 1 Day) ;
End if ;
If (Month(CHECKDATE) = 12
and year(CHECKDATE) = 9999 ) Then
SET LASTDATE = DATE('31.12.9999') ;
Else
SET LASTDATE = DATE((CHECKDATE + 1 MONTH
-DAY(CHECKDATE) DAY)) ;
End if ;
SET WORKDAY = 0 ;
WHILE EODATE = 0 DO
If DAYOFWEEK_ISO(FIRSTDATE) = WOCHENTAG
THEN
SET WORKDAY = WORKDAY + 1 ;
end if;
If FIRSTDATE >= LASTDATE Then
SET EODATE = 1;
Else
SET FIRSTDATE = FIRSTDATE +
1 Day;
End if ;
END WHILE;
RETURN WORKDAY ;
END;
AUFRUF :
select WOCHENTAGE(DATE('13.01.2011'), 1) as MO
, WOCHENTAGE(DATE('13.01.2011'), 2) as DI
, WOCHENTAGE(DATE('13.01.2011'), 3) as MI
, WOCHENTAGE(DATE('13.01.2011'), 4) as DO
, WOCHENTAGE(DATE('13.01.2011'), 5) as FR
from sysibm.sysdummy1;
Ergebniss : 5 4 4 4 4 (5*Montag 4*Dienstag - Freitag)
Ergebniss iss eine Tabelle
CREATE FUNCTION "DB2ADMIN"."FEIERTAGE"
("CHECKDATE" DATE )
RETURNS TABLE
("DATUM" DATE,
"FEIERTAG" VARCHAR(50),
"WOCHENTAG" VARCHAR(50)
)
SPECIFIC "DB2ADMIN"."FEIERTAGE"
LANGUAGE SQL
DETERMINISTIC
READS SQL DATA
STATIC DISPATCH
CALLED ON NULL INPUT
NO EXTERNAL ACTION
INHERIT SPECIAL REGISTERS
BEGIN ATOMIC
DECLARE OSTER_SONNTAG DATE ;
SET OSTER_SONNTAG = OSTERN(CHECKDATE) ;
RETURN
Select Datum, Feiertag, Wochentag
FROM(
Values (OSTER_SONNTAG - 52 Days , 'Weiberfastnacht')
,
(OSTER_SONNTAG - 48 Days, 'Rosenmontag')
,
(OSTER_SONNTAG - 2 Days , 'Karfreitag')
,
(OSTER_SONNTAG ,
'Ostersonnatg')
,
(OSTER_SONNTAG + 1 Days , 'Ostermontag')
,
(OSTER_SONNTAG + 39 Days , 'Himmelfahrt')
,
(OSTER_SONNTAG + 50 Days , 'Pfingstmontag')
,
(OSTER_SONNTAG + 60 Days , 'Fronleichnam')
,
(DATE('01.01.' concat char(Substr(DIGITS(year(CHECKDATE)), 7, 4), 4)),
'Neujahr' )
,
(DATE('31.12.' concat char(Substr(DIGITS(year(CHECKDATE)), 7, 4), 4)) ,
'Silvester' )
,
(DATE('24.12.' concat char(Substr(DIGITS(year(CHECKDATE)), 7, 4), 4)) ,
'Weihnachten' )
,
(DATE('25.12.' concat char(Substr(DIGITS(year(CHECKDATE)), 7, 4), 4)) ,
'1. Weihnachtsfeiertag')
,
(DATE('26.12.' concat char(Substr(DIGITS(year(CHECKDATE)), 7, 4), 4)) ,
'2. Weihnachtsfeiertag' )
,
(DATE('03.10.' concat char(Substr(DIGITS(year(CHECKDATE)), 7, 4), 4)),
'Tag der deutschen einheit')
,
(DATE('01.11.' concat char(Substr(DIGITS(year(CHECKDATE)), 7, 4), 4)) ,
'Allerheiligen')
) Feiertage (
Datum, Feiertag)
Inner Join(
Values (1, 'Sonntag') , (2, 'Montag') , (3, 'Dienstag')
, (4, 'Mittwoch') , (5, 'Donnerstag') , (6,
'Freitag')
, (7, 'Samstag')
) Tage (
DayInd, Wochentag)
On
DAYOFWEEK(Datum) = DayInd
;
END;
Aufruf :
select Char(Date(Datum), EUR), Feiertag, Wochentag
from table(Feiertage(Current Date)) T1
order by datum ;