SQL Example Archive - Blogofant

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

Verschlagwortet: SQL Example

SQL – Datum und Zeit – Tricks, Hacks & Tipps

SQL – Formatting Dates and Numbers – Tricks & Tipps

SQL IN Liste – Multi-value comparison IN List

Werbung / Advertisement

Top Ratings

Werbung / Advertisement

Werbung / Advertisement

Werbung / Advertisement

Werbung / Advertisement

Neueste Beiträge

Werbung / Advertisement

Werbung / Advertisement