DB2
z/OS DB2 LUW
z/OS
CICS VTAM
|
DB2 z/OS Info
|
Home |
Datumsberechnung
DB2 z/OS
Letzter
(arbeits) Tag eines Monats
(Mo-Fr)
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
, CASE WHEN DAYOFWEEK_ISO(LETZTER) > 5 THEN
LETZTER - (DAYOFWEEK_ISO(LETZTER) - 5) DAYS
ELSE
LETZTER
END AS LETZTER_WOCHENTAG
FROM TAGE;
;;
Arbeitstage und
WE eines Jahres
zählen
WITH
TAGE (TAG, TNAME) AS
(SELECT DATE('01.01.' CONCAT
char(year(Current date)))
, UCASE(DSN8.DAYNAME(CHAR(
DATE('01.01.' CONCAT char(year(Current date))), ISO)))
FROM SYSIBM.SYSDUMMY1
UNION ALL
SELECT DATE(TAG) + 1 DAY
, UCASE(DSN8.DAYNAME(CHAR(TAG + 1 DAY, ISO)))
FROM TAGE
WHERE YEAR(TAG) =
YEAR(CURRENT DATE)
)
SELECT SUM(CASE WHEN SUBSTR(TNAME, 1, 1) <> 'S' THEN 0 ELSE 1 END ) AS AT
, SUM(CASE WHEN SUBSTR(TNAME, 1, 1) =
'S' THEN 1 ELSE 0 END ) AS WE
, SUM(CASE WHEN SUBSTR(TNAME, 1, 2) = 'SA'
THEN 1 ELSE 0 END ) AS SA
, SUM(CASE WHEN SUBSTR(TNAME, 1, 2) = 'SU'
THEN 1 ELSE 0 END ) AS SO
, SUM(CASE WHEN SUBSTR(TNAME, 1, 2) = 'MO'
THEN 1 ELSE 0 END ) AS MO
, SUM(CASE WHEN SUBSTR(TNAME, 1, 2) = 'TU'
THEN 1 ELSE 0 END ) AS DI
, SUM(CASE WHEN
SUBSTR(TNAME, 1, 2) = 'WE' THEN 1 ELSE 0 END ) AS MI
, SUM(CASE WHEN SUBSTR(TNAME, 1, 2) = 'TH'
THEN 1 ELSE 0 END ) AS DO
, SUM(CASE WHEN SUBSTR(TNAME, 1, 2) = 'FR'
THEN 1 ELSE 0 END ) AS FR
FROM TAGE
WHERE YEAR(TAG) = YEAR(CURRENT DATE)
;
Wochentage
eines Jahres zählen
WITH
JAHR (JAHR) AS
(SELECT
DATE('01.01.' CONCAT
CHAR(YEAR(CURRENT DATE + 0 YEAR)))
FROM SYSIBM.SYSDUMMY1
)
, TAGE (TAG, TNR) AS
(SELECT
JAHR
,
DAYOFWEEK_ISO(JAHR)
FROM JAHR
UNION ALL
SELECT DATE(TAG) + 1 DAY
, DAYOFWEEK_ISO(DATE(TAG + 1 DAY))
FROM TAGE
WHERE YEAR(TAG + 1 DAY) =
YEAR(TAG)
)
SELECT CASE TNR WHEN 1 THEN 'MO'
WHEN 2 THEN 'DI'
WHEN 3 THEN 'MI'
WHEN 4 THEN 'DO'
WHEN 5 THEN 'FR'
WHEN 6 THEN 'SA'
WHEN 7 THEN 'SO'
END
, COUNT(*)
FROM TAGE
GROUP BY TNR
ORDER BY TNR
;
Zeiten berechnen mit Timestampdiff
WITH ZEITEN (STARTZEIT, ENDEZEIT, PAUSEN) AS
(
SELECT TIMESTAMP ('2011-06-30-08.00.00.000000')
, TIMESTAMP
('2011-06-30-18.30.00.000000')
, TIME ('00:30:00')
FROM SYSIBM.SYSDUMMY1
UNION ALL
SELECT TIMESTAMP ('2011-07-30-10.15.00.000000')
, TIMESTAMP
('2011-07-31-15.15.00.000000')
, TIME ('00:30:00')
FROM SYSIBM.SYSDUMMY1
UNION ALL
SELECT TIMESTAMP ('2011-05-30-07.00.00.000000')
, TIMESTAMP
('2011-05-30-12.00.00.000000')
, TIME ('00:00:00')
FROM SYSIBM.SYSDUMMY1)
, ZEITEN2 (tage, STARTZEIT, ENDEZEIT, PAUSEN) AS
(
SELECT TIMESTAMPDIFF (16, CHAR (ENDEZEIT - STARTZEIT)) AS
tage,
CASE WHEN TIMESTAMPDIFF (16, CHAR
(ENDEZEIT - STARTZEIT)) > 0
THEN
STARTZEIT + TIMESTAMPDIFF (16, CHAR(ENDEZEIT - STARTZEIT))
DAYS
ELSE STARTZEIT
END, ENDEZEIT, PAUSEN FROM ZEITEN)
SELECT Tage
, TIME ( CHAR(DIGITS(DECIMAL(TIMESTAMPDIFF(8,
CHAR (ENDEZEIT - STARTZEIT)), 2, 0)))
CONCAT ':'
CONCAT CHAR
(DIGITS(DECIMAL(MOD(TIMESTAMPDIFF (4,
CHAR (ENDEZEIT - STARTZEIT)), 60), 2, 0)))
CONCAT ':'
CONCAT CHAR(DIGITS(DECIMAL(MOD(
TIMESTAMPDIFF (2,
CHAR (ENDEZEIT - STARTZEIT)), 60), 2, 0))))
- ((HOUR(TIME(PAUSEN)) * 60) +
MINUTE(TIME(PAUSEN))) MINUTE AS STUNDEN
FROM ZEITEN2
;
Anzahl Tage eines Jahres
WITH TAG (JAHR) AS
( SELECT
DATE('31.01.1960') FROM SYSIBM.SYSDUMMY1
UNION ALL
SELECT JAHR + 1 year
FROM
TAG
WHERE
year(Jahr) < 2100
)
SELECT CHAR(JAHR, EUR) AS JAHR,
DAYS(DATE('31.12.' CONCAT
CHAR(YEAR(JAHR))))
- DAYS(DATE('01.01.' CONCAT
CHAR(YEAR(JAHR))))
+ 1 AS ANZTAGE
FROM TAG
;