SQL – Datum und Zeit – Tricks, Hacks & Tipps

-- Example to generate dates
SELECT vDate
FROM (SELECT LEVEL, TRUNC(SYSDATE) - LEVEL + 1 AS vDate
      FROM DUAL
      CONNECT BY LEVEL <= 20)
ORDER BY vDate;
 

-- Example to generate 12 Months
SELECT TO_CHAR(vDate, 'MONTH') vMonthValues
FROM (SELECT LEVEL, ADD_MONTHS(TRUNC(SYSDATE, 'YEAR'), -LEVEL) AS vDate
      FROM DUAL
      CONNECT BY LEVEL <= 12 )
ORDER BY vDate;
 

-- Exmaple to generate month dates (first day of month)
SELECT vFirstDayOfLast12Months
FROM (SELECT LEVEL, ADD_MONTHS(TRUNC(SYSDATE, 'MONTH'), (LEVEL - 1)*-1) AS vDate
      FROM DUAL
      CONNECT BY LEVEL <= 20 )
ORDER BY vDate;
 

-- Exmaple to generate month dates (last day of month)
SELECT vLastDayOfLast12Months
FROM (SELECT LEVEL, ADD_MONTHS(TRUNC(SYSDATE, 'MONTH'), (LEVEL - 2)*-1)-1 AS vDate
      FROM DUAL
      CONNECT BY LEVEL <= 20 )
ORDER BY vDate;
 

-- Example to find MONDAY since 2019
SELECT vdate, TO_CHAR(vdate,'DY')
FROM ( SELECT TO_DATE('01.01.2019','DD.MM.YYYY')+LEVEL-1 vdate
      FROM DUAL
      CONNECT BY LEVEL <= ( SYSDATE - TO_DATE('01.01.2019','DD.MM.YYYY')+1 )
    )
WHERE TO_CHAR(vdate, 'DY') = 'MO';
 

-- Example to find first Monday in the year 2019
SELECT vdate, TO_CHAR(vdate,'DY')
FROM ( SELECT TO_DATE('01.01.2019','DD.MM.YYYY')+LEVEL-1 vdate
      FROM DUAL
      CONNECT BY LEVEL <= ( TO_DATE('31.12.2019','DD.MM.YYYY') - TO_DATE('01.01.2019','DD.MM.YYYY')+1 )
    )
WHERE TO_CHAR(vdate, 'DY') = 'MO'
ORDER BY vdate
FETCH NEXT 1 ROWS ONLY; -- same as LIMIT 1
 

-- Example to find last Monday in the year 2019
SELECT vdate, TO_CHAR(vdate,'DY')
FROM ( SELECT TO_DATE('01.01.2019','DD.MM.YYYY')+LEVEL-1 vdate
      FROM DUAL
      CONNECT BY LEVEL <= ( TO_DATE('31.12.2019','DD.MM.YYYY') - TO_DATE('01.01.2019','DD.MM.YYYY')+1 )
    )
WHERE TO_CHAR(vdate, 'DY') = 'MO'
ORDER BY vdate DESC
FETCH NEXT 1 ROWS ONLY; -- same as LIMIT 1
 

-- Example to get last day of the month
SELECT TRUNC(LAST_DAY(SYSDATE)) AS vLastDayOfMonth
FROM DUAL;
 

-- Example to first day of the month
SELECT TRUNC(SYSDATE, 'MONTH') AS vFirstDayOfMonth
FROM DUAL;
 

-- Example to get last day of the year
SELECT ADD_MONTHS(TRUNC(SYSDATE, 'YEAR'), 12) - 1 AS vLastDayOfYear
FROM DUAL;
 

-- Example to get the number of days in current month
SELECT CAST(TO_CHAR(LAST_DAY(SYSDATE), 'dd') AS INT) AS vNumberOfDaysInMonth
FROM DUAL;
 

-- Example to get the number of days per month
SELECT TO_CHAR(vdate, 'fmMONTH: ') || CAST(TO_CHAR(LAST_DAY(vDate), 'dd') AS INT) AS vNumberOfDaysInMonth
FROM (SELECT LEVEL, ADD_MONTHS(TRUNC(SYSDATE, 'YEAR'), LEVEL - 1) AS vDate
      FROM DUAL
      CONNECT BY LEVEL <= 12 )
ORDER BY vDate;
 

-- Example to get number of days left in current monnth
SELECT SYSDATE vToday, LAST_DAY(SYSDATE) AS vLastDayOfMonth, LAST_DAY(SYSDATE) - SYSDATE AS vDaysLeft
FROM DUAL;
 

-- Example to get number of days left in current year
SELECT SYSDATE vToday, ADD_MONTHS(TRUNC(SYSDATE,'YEAR'),12)-1 AS vLastDayOfYear, ROUND(ADD_MONTHS(TRUNC(SYSDATE,'YEAR'),12)-1 - SYSDATE) AS vDaysLeft
FROM DUAL;
 

-- Example to get number of days between 2 dates
SELECT ROUND((MONTHS_BETWEEN('31.12.2020', '11.09.2020') * 30.5), 0) AS vNumOfDays -- approximation with 30.5 days/month
FROM DUAL;
 

-- Get the number of seconds since morning / from midnigt
SELECT (SYSDATE - TRUNC(SYSDATE)) * 24 * 60 * 60 AS vNumberSecondsSinceMorning
FROM DUAL;
 

-- Get the number of seconds till midnight / end of the day
SELECT (TRUNC(SYSDATE+1) - SYSDATE) * 24 * 60 * 60 AS VNumberSeccondsLeft
FROM DUAL;
 

Mehr über SQL

Mehr zum Thema SQL findest du auf der Übersichtsseite über Datenbanken, SQL und PL/SQL sowie auf der Seite über SQL mit vielen Beispiel SQL Statements. Solltest du Formatierungen vornehmen wollen, dann kannst du dich im Beitrag über Formatierung von Nummern und Datumswerten in SQL informieren.