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
;